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).