here is the new code, the ideal would be to create an ssis custom component but that would be next, in the meantime I'm enjoying working at Fanatics. Ah... before I forget I would be a speaker for SqlSaturday 2015 in Tampa....Thank You ....
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Text;
namespace ST_78e778bf841c4fed8969b1315fe198d0.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Global Variables
String Archive = "";
String Import = "";
String Load = "";
String Hold = "";
String Scrubber = "";
String Expression = @"[^\u0000-\u007F]";
Encoding ascii = Encoding.ASCII;
int Rows2Process = 100000;
Variables vars = null;
#endregion
public void Main()
{
// TODO: Add your code here
initialized();
MoveFiles(Import, Scrubber);
FileScrubber(Scrubber);
MoveFiles(Hold, Load);
Dts.TaskResult = (int)ScriptResults.Success;
}
// Initialize Variables
private void initialized()
{
Dts.VariableDispenser.LockForRead("User::strArchive");
Dts.VariableDispenser.LockForRead("User::strImport");
Dts.VariableDispenser.LockForRead("User::strLoad");
Dts.VariableDispenser.LockForRead("User::strHold");
Dts.VariableDispenser.LockForRead("User::strScrubber");
Dts.VariableDispenser.GetVariables(ref vars);
Archive = vars["User::strArchive"].Value.ToString();
Import = vars["User::strImport"].Value.ToString();
Load = vars["User::strLoad"].Value.ToString();
Hold = vars["User::strHold"].Value.ToString();
Scrubber = vars["User::strScrubber"].Value.ToString();
}
// Start Scrubbing Mode
private void FileScrubber(String DirectoryPath)
{
List<String> _FileList = GetDirectoryFiles(DirectoryPath);
// Create the new Empty File on the Hold Directory
List<String> _Rows2Clean = new List<string>();
foreach (String FullFilePath in _FileList)
{
int RecordCnt = 0;
String FileName = GetFileName(FullFilePath);
String NewPath = Hold + "\\" + FileName;
// CreateEmptyFile(NewPath);
using (FileStream sf = File.Open(FullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
StreamReader sr = new StreamReader(sf);
String row = string.Empty;
while ((row = sr.ReadLine()) != null)
{
_Rows2Clean.Add(row);
RecordCnt++;
if (_Rows2Clean.Count >= Rows2Process)
{
WriteNewFile(ref _Rows2Clean, NewPath);
_Rows2Clean.Clear();
}
}
sf.Close();
sr.Dispose();
}
if (_Rows2Clean.Count >= 1)
{
WriteNewFile(ref _Rows2Clean, NewPath);
_Rows2Clean.Clear();
}
//MessageBox.Show(@"Records Count:" + RecordCnt.ToString() + " FileName " + FileName);
File.Move(FullFilePath, Archive+"\\scrub_"+FileName);
NewPath = null;
FileName = null;
}
_FileList.Clear();
_FileList = null;
_Rows2Clean.Clear();
_Rows2Clean = null;
}
private String GetFileName(String FullFilePath)
{
FileInfo _fileInfo = new FileInfo(FullFilePath);
String FileName = String.Empty;
if (_fileInfo.Exists)
{
FileName = _fileInfo.Name.ToString().Trim();
}
_fileInfo = null;
return FileName;
}
private String RemoveUnicode(String inputString)
{
string asAscii = Encoding.ASCII.GetString(
Encoding.Convert(
Encoding.UTF8,
Encoding.GetEncoding(
Encoding.ASCII.EncodingName,
new EncoderReplacementFallback(string.Empty),
new DecoderExceptionFallback()
),
Encoding.UTF8.GetBytes(inputString)
)
);
return asAscii;
}
private void WriteNewFile(ref List<String> RowsToWrite, String FullFilePath)
{
//s = Regex.Replace(s, @"[^A-Za-z0-9]", "");
List<String> NewRows2Write = new List<String>();
foreach (String Row in RowsToWrite)
{
String regRow = Regex.Replace(Row, Expression, "");
NewRows2Write.Add(RemoveUnicode(regRow));
regRow = String.Empty;
}
RowsToWrite.Clear();
using (FileStream sf = File.Open(FullFilePath, FileMode.Append, FileAccess.Write, FileShare.None))
{
using (StreamWriter sw = new StreamWriter(sf))
{
foreach (String newRow in NewRows2Write)
{
sw.WriteLine(newRow);
}
sw.Dispose();
NewRows2Write.Clear();
NewRows2Write = null;
}
sf.Close();
}
}
private void CreateEmptyFile(String FullFilePath)
{
FileInfo _file = new FileInfo(FullFilePath);
if (!_file.Exists)
{
_file.Create();
_file = null;
}
else
{
_file = null;
}
}
// Create a list of files in the Import Folder for scrubbing
private List<String> GetDirectoryFiles(String FullFilePath)
{
String[] fdir = Directory.GetFiles(FullFilePath);
List<String> files = new List<string>(fdir);
fdir = null;
return files;
}
// Move Files to Load Directory
private void MoveFiles(String FromPath, String ToPath)
{
List<String> FromFiles = GetDirectoryFiles(FromPath);
foreach (String FullFilePath in FromFiles)
{
Boolean IsLock = IsFileLocked(FullFilePath);
if (!IsLock)
{
FileInfo _finfo = new FileInfo(FullFilePath);
FileInfo _scF = new FileInfo(ToPath + "\\" + _finfo.Name.ToString());
if (!_scF.Exists)
{
_finfo.MoveTo(_scF.FullName);
}
else
{
File.Delete(_scF.FullName);
_finfo.MoveTo(_scF.FullName);
}
}
}
FromFiles = null;
}
//Check if Files are lock If lock file won't be move to scrubber
protected virtual bool IsFileLocked(String FullFilePath)
{
FileStream stream = null;
try
{
stream = File.Open(FullFilePath, FileMode.Open, FileAccess.ReadWrite, FileShare.None);
}
catch (IOException)
{
//the file is unavailable because it is:
//still being written to
//or being processed by another thread
//or does not exist (has already been processed)
return true;
}
finally
{
if (stream != null)
stream.Close();
}
//file is not locked
return false;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
