Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Does File Exist Check in SSIS

A very common need in SSIS is to check to see if a file exist before you run what could be a very long process in your package.  There are no native tasks inside SSIS that can do this check but you can accomplish this using a Script Task.  Here are the steps to check to see if a file exist.  To view this blog with screenshots visit my regular blog at http://blogs.pragmaticworks.com/devin_knight/.

1.  Setup two variables.  The variable named strFileLocation has a string data type with the value being the location of the file I want to check for.  The variable bolFileExists has a boolean data type with the value changing based on whether the file exist or not.  If the file is found the value will be changed to True otherwise it stays False.

2.  Use a Script Task in the Control Flow and set the ReadOnlyVariables to use the strFileLocation variable and the ReadWriteVariables to use the bolFileExists variable then select Edit Script.

3.  There are two methods for writing this script.  The first method you must first add the namespace System.IO.  The second method does not require this.

4.  Scroll down the editor until you find the green commented out text that says Add your code here.  Replace that with the following code then save and close the editor :

Dts.Variables("bolFileExists").Value = File.Exists(Dts.Variables("strFileLocation").Value)

The second option for the script is (Remember this option is not using the Imports System.IO step) :

Dts.Variables("bolFileExists").Value = My.Computer.FileSystem.FileExists(Dts.Variables("strFileLocation").Value) 

5.  Now that you have the script done you can use things more familiar to you in SSIS like precedence constraints with expressions to get the desired results.  Connect the completed Script Task to a path that you want the package to move if the file does exist.  Open the precedence constraints editor by double clicking on the line. change the Evaluation operation to Expression and Constraint and add the expression @bolFileExists==True.  Remember the double equals is a comparison.  If it was a single equal sign it would be trying to set the value of the variable to True.  Click OK once complete.  If you want a path for if the file is not found then follow the same steps with the precedence constraint but the expression should be @bolFileExists==False.

I’m using script tasks just as placeholders here for how the rest of the package may look.  If my file did exist it would go down the left path.  If it did not exist my package flow would go right.

Comments

Posted by Alasdair Thomson on 14 August 2009

A good post, I too had to do a similar package to be used as a child package in our ETL packages. Although I used the DTS script task to detail all the files in a folder location and write this info to a table in SQL, this way we can  track what was loaded and when etc (as well as using ETL logs).

Posted by Jason on 14 August 2009

I have a similar problem I am trying to fix related to the detection and removal of directories within a system.  In certain cases I want to scan for the directories only, in other cases I want to determine if the directory exists, and then if files of a certain type are present.  If the directory is blank (or in certain cases even if it has data) I want to delete the directory.  Because the directories follow a date/time naming convention and I could be scanning across a large set of dates, it is difficult to determine what the folder name(s) might be.

Anyone have a good way to scan a main directory for existing sub-directory trees (then in certain cases verify contents) before deleting those sub-directory branches?

Thanks in advance  :)

Posted by Michael Sallmen on 14 August 2009

Devin,

Thanks for a great tip!

westmorj,

There are a lot of things that you might consider.  To start, try using Directory.Exists() where Devin is using File.Exists().

Posted by knight_devin@hotmail.com on 17 August 2009

Westmorj,

Sounds like Michael has a good solution for you.  Let me know if you need anymore help.

Posted by knight_devin@hotmail.com on 17 August 2009

Alasdair,

I love the sounds of that.  Auditing is music to my ears.  I'm a big proponent of doing some form of auditing where ever possible.

Posted by artyom on 21 June 2011

Thanks for this post.

Can somebody help me to mix this File Exists Check with Regular Expression:

if i need to check existing of some type like:

Load_Type1_20110501.xlsx

Load_Type1_20110502.xlsx

Leave a Comment

Please register or log in to leave a comment.