Have you ever had an SSIS package fail because a source file it reads from was opened somewhere else? This can be especially frustrating if the package in question runs after hours or on weekends. In this post, I'll demonstrate how to avoid this issue with the Task Factory File Properties Task, the Task Factory Timer Task and a For Loop container.
First, I start with the data flow task. In the data flow, I have a Flat File source and sending the data through some data cleansing transforms. What I am doing with the data isn’t important for this example; just focus on that file source.
The source file I am using is just a list of company names and contacts in a CSV file. Let’s say someone opens the file before your package is scheduled to run. If they still have it opened when the package starts, you end up with this nice little message:
To avoid this, in the control I will use the Files Properties task. This task will read any property of a file and store that value in a variable. When configuring the task, choose ‘Connection Manager’ as your source path, and then select the connection manager to the flat file.
Find the property of ‘FileInUse’. Click the drop down menu and select ‘Create new variable’. I have named this variable ‘bolFileInUse’. Be sure to set the value to True.
Click OK to close out of the variable editor and OK again to close out of the File Properties task editor. Next add the Task Factory Timer task and connect it after the File Properties task. This task will let you set a time delay on the package that can use a hard coded value or read the value from a variable. For this example, I am only going to set the timer for 5 seconds.
Click OK to close the editor. Now, I want to put this into a For Loop container. Drag in the container and put the file properties task and the timer task inside and connect it to the data flow task. It should look something like this:
Open the For Loop container. The only thing I need to do is set the EvalExpression field. The expression I use here is @bolFileInUse==True.
Now we can test it out. Open the CSV source file and then run the package. The File Properties task will check if the file is in use and then move on to the timer task. After that, it loops back to the properties task as long as the file is open. If you view the progress tab, this is what you will see:
Close the file and the package will continue on to the dataflow.
In this example, I put the File Properties Task before the Timer Task, but you can reverse it as well. The main objective of using these two tasks in this way is to eliminate the possibility of a package failing because of source files not being available. Get an in-depth demo of the File Properties Task by watching the following video: