Tuesday, October 13, 2009

Dabbling with FileExists and SSIS packages

So I've been trolling around through the www trying to find a quick (and dirty way) to determine if a file exists before executing some SQL. I found a few articles through the wonders of googling and there are a lot of ways (SSIS:: Test for Data Files Existence, Determining if a File Exists in SSIS Using Wildcards, Check if File Exists, Does File Exist Check in SSIS) to skin this cat, but I felt there had to be a simpler way.

Basically, I ended up using the "Set Attributes" operation in a File System Task Editor and an existing SourceConnection.

There are a few assumptions here...
1. The source connection file should exist when first creating the connection.
2. Setting all of the attributes (hidden, read-only, archive, system) to False is ok.
3. You don't mind hacks.

So here are the steps you can take to check if one or more file exists before executing your SQL:
1. Set up a new file connection pointing to the file to be loaded.
2. Create a File System Task using the "Set Attributes" operation.
3. Add your precedence constraint/connectors.

Test the steps by executing the task once (should turn green). Then rename the source file and executing the step again (should turn red).

I haven't tested this in a production environment but figured I'd write it down and see if it works for others.

Good luck and happy coding!