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!

Wednesday, June 3, 2009

DSN-less ColdFusion queries

// DNSless DB connection
// http://www.datapackethelp.net/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=47&nav=0
// http://www.ibmpressbooks.com/articles/printerfriendly.asp?p=170336
classLoader = createObject("java", "java.lang.Class");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection("jdbc:macromedia:db2://myserver.com:55000;DatabaseName=SMWDB","username","password");

st = con.createStatement();
rs = st.ExecuteQuery(PreserveSingleQuotes(mySql));
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);