A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, July 7, 2008

To hook or not to hook

And of course the response is "Hookers are insane, I'll gladly stay in the second row"

I thought SMO was going to hold the answer to how to save objects to SQL Server but that didn't turn out to be the case so all the work I've been doing on the SSISUploader has been based on me hooking the command line thinger and building an object model on top of that. It's been going well, I've gotten in and messed with some stuff I had not touched before in the .NET world. I've got a working concept out there and it seems to be getting some download activity. I've started getting a testing harness wrapped around it so I'll know when I break something trying to build a more coherent pattern around it.

I thought I'd take a night or two off from it and try to scratch a different itch---COBOL copy books. We consume a good deal of data from a mainframe (oh how deliciously wrong it is to be using cutting edge ETL tools on data from a 40+ year old system) and that means I have to spend a good deal of time dealing with fixed-width flat files. Great big pain in my ass when I have a 2000 byte wide file with 300+ columns in it and no headers. I'd built one tool to dummy up data so visually I could see where the columns change over thus

C0 C1 C2 C3

It works well enough for what it's doing but it still doesn't spare me the pain of having to transcribe a few hundred field names into the click happy connection manager interface. So I dive into BOL and find out about creating a connection manager dynamically, loading my package from XML which will be nice as I can just read in an existing package and slap a new connection manager into. And then I see some methods I was looking for back before I started hooking dtutil. Look at the object model on Microsoft.SqlServer.Dts.Runtime.Application

Microsoft.SqlServer.Dts.Runtime.Application application = null;
application = new Microsoft.SqlServer.Dts.Runtime.Application();
application.CreateFolderOnDtsServer("\\MSDB", "HeyLookAtMe", "localhost");

I think I'm going to turn in early. Maybe I'll get up and get some miles in before work, less than 60 days 'til the MS 150. Oh well, if I end up gutting the dtutil hook, it will still have provided a functionality that the object model doesn't provide and that is the simulate portion. People would still be able to use my tool to figure out what the commands they can pass in to dtutil from a scripting perspective.

No comments: