Decision making in SSIS depending upon condition

SSIS(SQL Server Integration Service) is not much flexible in the case of decision making based on a condition. In many cases you feel the need of a “Condition” block that can be used in the control flow to decide the alternative paths. For example if you want to copy a file named “AnyFile.txt” into a folder named “Current” if the file does not exist in a folder named “Previous”, otherwise move the file to a folder named “Archive”, there is no direct way to test the “If File Exists in Previous Folder” condition. One possible solution is to use the “Script Task”. The steps are as follows:

1. Declare a package level Boolean variable named “IsFileExistsInPrevious” for example.

2. Create a script task and pass the “IsFileExistsInPrevious” variable as “ReadWriteVariable” into it. Pass other “ReadOnly” variables that contain the name of the file if necessary.

3. Using the components in the .NET “System.IO” namespace, check the existence of the file in the “Previous” folder. If the file exists, set the value of the “IsFileExistsInPrevious” variables to “True”. Otherwise set it to “False”.

VB .NET Script:

Dim srcFilePath As String = ""

If File.Exists(srcFilePath ) Then
Dts.Variables("IsFileExistsInPrevious").Value = True
Else
Dts.Variables("IsFileExistsInPrevious").Value = False

End If
Dts.TaskResult = Dts.Results.Success

4. Declare two “File System” tasks that copies files to the “Current” or “Archive” folders depending on the result of the previous “Script Task”. Drag the “Precedence Constraint” line from the previous “Script Task” to any of the following “File System” tasks.

5. Right click the “Precedence Constraint” line and Select “Edit”. In the “Precedence Constraint Editor” window, Select the value of “Evaluation Operation” to “Expression”. Type the following into the “Expression” box.

@[User::IsFileExistsInPrevious]==false

6. Drag the “Precedence Constraint line to the “File System” task that copies the file to the “Current” folder.

7. Right click the “Precedence Constraint” line again and Select “Edit”. In the “Precedence Constraint Editor” window, Select the value of “Evaluation Operation” to “Expression”. Type the following into the “Expression” box.

@[User::IsFileExistsInPrevious]==true

8. Drag this “Precedence Constraint line to the “File System” task that copies the file to the “Archive” folder.

The key point to note here is the usage of the “Precedence Constraint” editor.The “Expression” option enables to select alternative paths depending on condition (the value of a user variable “IsFileExistsInPrevious” in this case).

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 .