Have you ever encountered a situation where your SSIS package fails because a required file is not available at the expected time? It can be frustrating to deal with such delays and reprocess the package multiple times. In this article, we will explore a solution to this problem by configuring SSIS to wait for a file to become available within a specified time period.

The Process

Follow these steps to set up the file check in your SSIS package:

1. Open the Microsoft Business Intelligence Studio and create a new Integration Services project.
2. Create a connection for the file and copy the location from the filename. Set the “Delay Validation” property of the file connection to true.
3. Create a new variable using the Menu Options: View, Other Windows, Variables. Name the variable as desired, but it must be of string data type. Set the value of the variable as the location of the file.
4. Go back to the properties of the Flat File Connection Manager and click on the expressions options. Set the expression property to ConnectionString and open the expression. Drag the variable from the drop-down variables into the expression area. Click “Evaluate expression” to verify the value of the variable and click OK.
5. Drag a script task from the toolbox and double click on it. Set the read-only variables to the flat file variable created earlier and set the script to VB Script.
6. Click on “Edit Script” and paste the provided code into the script editor.
7. Make sure to edit the “While True” section of the code to match your variable name: System.IO.File.Exists(Dts.Variables("FlatFileConnection").Value.ToString)
8. To adjust the time range, modify the line marked Dim NewDT As DateTime = Dt.AddHours(4) by changing the number in Dt.AddHours().
9. Click on “Exit” and press OK.

The Conclusion

By following these steps, you can create a flat file connection and use a variable to specify the file path and name. With the VB script task, you can instruct SSIS to keep checking for the file within the specified time range. Whether you need to wait for an hour, two hours, or even 23 hours, this solution allows you to handle delays in file transfers without the need for manual reprocessing or reexecution of the SSIS package.

With this approach, you can ensure that your SSIS package waits for the required file before starting the processing, reducing the chances of failures due to missing files.