Dynamic SSIS Packages, Part 5. Application.SaveToXML method for debugging

On my little “Dynamic SSIS Packages” series, I’m getting some hits but not a lot of comments or questions/feedback. Is this concept too difficult, or did I not do a good job explaining? I think not the latter and surely not the former either, because I spent three or four posts just describing what the concept was and what we were trying to do. And, we haven’t really coded yet, but rather just completed setup and execution.  The delivered stuff that I have in previous posts should load and run without too much problem, and I hope if interested that you take the time to go through this process – I’ve been working on DTS since SQL 7, and SSIS since before SQL Server 2005 came out via the earliest CTP, and I guarantee that you’ll like these packages. They’re super cool, fast, and stable.  I’ve said before that I believe they are the slickest SSIS packages that I’ve ever seen, and still feel that way. For importing or exporting data, this dynamic package kicks-ass!

Besides F8 “Step Into” for debugging SSIS code works great, and while our ‘infant’ package does not have any included for debugging (you have to write your own to get bugs to actually print),  a neat trick to add is the Application.SaveToXml Method. This is pretty slick because if the package makes it all the way through, but then bombs on the pkg.Execute() step, you’re gold because you can take the saved package, import it into BIDS, and then go look at it to see where the problem may be.  You can even run it to test what you believe you need to code to implement the fix.  Here is the part to save out the package, fairly simple:

' Save the package to a .dtsx file.

             app.SaveToXml("C:\temp\SQLToFileX.dtsx",pkg, Nothing)

Locate the app.SaveToXml call within the script task code. I can take the SQLToFileX.dtsx package, add it to Visual Studio, and inspect it more closely after the package has run. Note that for each loop in your ForEach Loop, you will get a new package, and the code will overwrite the previous. So if you have 10 rows in your configuration tables,which equals 10 exports, but the 10^th fails, only the 10^th package will be saved to c:\temp\SQLToFileX.dtsx, and then you can go look at that one to try and figure out what went wrong.

Lee

-------------