SSISUploader

In the immortal words ofDr. Frankenstein, "It is alive!" My first open source contribution, beyond just monitoring assorted mailing lists---ant, I'm looking at you, was published this evening. SSISUploader is an attempt to take the dtutil tool and make it more command line friendly, at least for SQL Server deploys. It's after midnight and the past week has been like this coding on it so I best turn in but I wanted to let google start linking it in case it'll be of use to someone else.

SUBST, friend or foe

SUBST, for those that don't know is a command in Windows that allows one to SUBSTitute one drive letter for some path. For example, if I liked K better for my C drive, I could Start->Run and then type in SUBST K: C:\ and whammo, I now have a K drive that shows up in windows explorer and I can do things like create a file on K:\Windows and it'll show up on both the C drive as well as the K drive. Big whoop, right? It can be handy in situations where our servers expect data to be on the D drive and the local developers only have a C. Data locations are driven by configuration files so it's not bad except when you do a fresh install and forget to update that file. Trivial but hey, this is just a trick, not rocket surgery or life altering.

However, not everything plays well with it. The first thing I noticed was that when I attach a thumb drive to my machine, it will take the next available drive. SUBST doesn't _really_ mark that drive letter as taken so if K was your next available drive and you've mapped to it, connect a thumb drive which also claims K and then try to access K. Windows will get all sorts of confused as to what to do with it. At this point, you need to remove the substituted drive and K will be available for your removable media. To knock down a substitution, the command would be SUBST /D K:

The next interesting thing is defrag. Disk defragment, well if you're reading this I'm going to assume you not only know where it it and what it does. So, right click on your K drive and it'll say Local Disk (K:) Properties and you can defrag but wait, what's this defrag is smart enough to know that we're really only talking about that hard drive there and it's called C:

The last item I'll comment on is the one that actually drove this post. We send email through a stored procedure called sp_send_dbmail in our SSIS packages. I'm not convinced it's the best approach but it works and it's in place so we do it. I was trying to diagnose an issue and had this call

EXECUTE msdb.dbo.sp_send_dbmail 
    @profile_name = 'SQLPROFILE'
,   @recipients = 'someone@somewhere.com'
,   @file_attachments = 'K:\data\somefile.txt'
,   @subject = 'Something report'
,   @body = 'Attached is the file containing something.'

It errored out with the following

Msg 22051, Level 16, State 1, Line 0
Attachment file D:\SSISData\Concur\Output\ConcurBlankSupervisorReport.csv is invalid.

I copied that path and in the open file dialog from notepad pasted that in. The file opened fine. I reran the stored procedure with C instead of the K drive and voila, it works like a champ. My initial interblag browsings found someone having issues using a file on a networked drive getting the same error so maybe the stored proc isn't as wise as defrag. Oh well, plenty of work to do but thought I'd capture this in case it helps someone. I'm sure BOL documents this behaviour but it's not on my critical path at the moment.

Current project---SSISUploader a dtutil hook

At my current engagement, we deploy SSIS packages to SQL Server. It is not the SSIS package store, but just SQL Server so they go into the msdb database. How that happens is through a manifest file that loads packages all into a single folder. Now, you can create folders and do all sorts of package management from the command line with the program dtutil.exe What'd be nice would be to have the ability to use the nice manifest file to specify "these are the packages I want to deploy" but to deploy them into a folder in the database and by the way, nuke anything that used to be in there" We've got old packages in production that would probably fail if they managed to get run but just on principle, they should be removed whenever we do a deploy.

So, more posts will be coming on this project I'm sure, but for now, I just wanted to taunt Microsoft's spelling champs.
"Error (0xC0014043) createing folder recursive1 in folder recursive0." Obviously I've boned something or I wouldn't be seeing that but my code is so far from alpha, I'm not worried.

The project will eventually be on codeplex, I've already staked out a project site http://www.codeplex.com/SSISUploader