Setting the FTP password from external configuration sources in a SSIS package

SSIS(SQL Server Integration Service) is a great ETL(Extract Transform Load) tool from Microsoft.It has many useful features for integrating data from different sources.One of the handy feature is the "FTP Task" that enables downloading files from a remote FTP address. The "FTP Task" uses a "FTP Connection Manager" component that stores the FTP credentials(URL,User,Password..). Instead of hard coding the credentials into the component, we can use “Expressions” feature of SSIS to set those properties from configuration files,registry entries or environment variables. So we can set the FTP URL,port,user name from the configuration sources.Unfortunately and surprisingly there is no option in the “Expressions” list to set the FTP password.The “Server Password” option just does not appear in the “Expression” List.One work around of this problem is to use the “Script Task” component and set the “Server Password” property of the “FTP Connection Manager” component. The VB .NET code (SQL 2005 only supports VB .NET) snippet below does the following:

1.Accesses the “FTP Connection Manager” component.
2.Sets the “ServerPassword” property with the value of a package variable(“FTPPassword” here).The package variable can be set from any external configuration sources supported by SSIS(XML file,Environment Variable,Registry..).


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim FTPConnectionManager As ConnectionManager


FTPConnectionManager = Dts.Connections("FTP_Conn")


FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager,
Dts.Variables("FTPPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class


Note:The “Script Task” component must execute before the “FTP Task” component .

Tracking the completion of a file upload or copy using .NET FileSystemWatcher component

I had an assignment to process the files uploaded by users into a FTP location.Files used be removed removed after the processing was complete.I had developed a .NET Windows Service in the FTP server machine to watch the FTP directory for the uploaded files.The service used to track the files as soon uploaded and start processing.I had used the .NET "FileSystemWatcher" component to monitor the directory.The problem was that,as soon the file was created in the directory(the upload by user had just started), the service used to start processing it.Normally it takes a while for the completion of file upload specially if the size is larger.So the service was processing incomplete files before the upload is complete.After exploring the property,methods and events of the extremely useful "FileSystemWatcher" component, i found the solution to track when the file is completely uploaded.

To mark the upload completion, a temporary file with a ".flag" extension used to be created that matches exactly the name of the uploaded file.For example if the file name is "Data.zip", the name of the temporary file would be "Data.zip.flag".Any valid and unused extension other than ".flag" could have been used here.The service code is now changed to look for the files with ".flag" extension to understand that the file is fully uploaded or copied.It then extracts the file name from the ".flag" file name and starts the normal processing.

Here is the code:

static void Main(string[] args)
{
FileSystemWatcher watcher = new FileSystemWatcher();
watcher.Path = "C:\\Temp";
watcher.Filter = "*.zip";
watcher.NotifyFilter = (NotifyFilters.LastWrite);
watcher.Changed += new FileSystemEventHandler(watcher_Changed);
watcher.EnableRaisingEvents = true;
Console.ReadLine();
}

static void watcher_Changed(object sender, FileSystemEventArgs e)
{
FileInfo fi = new FileInfo(e.FullPath);

if (fi.Length > 0 && !File.Exists(e.FullPath + ".flag"))
{

File.Create(e.FullPath + ".flag");

}

}

The "path" property of "FileSystemWatcher" component is used to mention the directory to watch for and the "filter" property is used to mention file name or file types.But the crucial property for this particular task is the "NotifyFilter" property that is set to "NotifyFilters.LastWrite" here. This will enable the component to notify when the final byte of the file is written to disk.

The "changed" event of the component is handled to get notified when the file upload is complete.Here we have checked the file size to be sure that it is not zero and also checking whether the temporary file exists.If the file has a size(greater than zero) and temporary file does not exist, we are creating the temporary file.The same event is fired multiple times by .NET Framework.So the checking is being done to create the temporary file only once.