Wednesday, May 25, 2011

Flat Files Archiving in SSIS

In Microsoft Visual C# 2008:

2 Parameters need to declare:
FlatFilePath: String Type (To hold the URL)
SourceFileDir: String Type (To hold the Source folder URL)


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_f580a072cab2401eab429eeca3b97e74.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
int len = Dts.Variables["FlatFilePath"].Value.ToString().Length;
string ActiveDir = Dts.Variables["FlatFilePath"].Value.ToString().Substring(0,len -1);
string NewPath = System.IO.Path.Combine(ActiveDir, "ArchFolderName" + string.Format("_{0:yyyy-MM-dd_hh-mm-ss}", DateTime.Now));

System.IO.Directory.CreateDirectory(NewPath);

DirectoryInfo dirSrc = new DirectoryInfo(Dts.Variables["NewSourceFileDir"].Value.ToString());
DirectoryInfo dirDest = new DirectoryInfo(NewPath + "\\");
FileInfo[] Files = dirSrc.GetFiles();
if (Files.Length > 0)
{
foreach (FileInfo aFile in Files)
{
if (File.Exists(dirDest + aFile.Name))
{
File.Delete(dirDest + aFile.Name);
}
aFile.MoveTo(dirDest + aFile.Name);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}