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!
Tuesday, October 13, 2009
Wednesday, June 3, 2009
<cfscript> // 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"); classLoader.forName("macromedia.jdbc.MacromediaDriver"); 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); st.close(); con.close(); </cfscript>