Project Description
This goal of this project is to create a stronger command line deploy utility for SSIS packages destined for SQL server. Capabilities will include purging of existing content, creation and deployment to subfolders and uploads based off deployment manifest generated by Visual Studio.

2008-09-02: While moving very slowly, this project is still alive. Code checked in this evening covers an alternate approach for interacting with SQL Server.

Why: Getting SQL Server Integration Services (SSIS) packages into SQL Server is more painful that it should be. The project in Visual Studio/BIDS allows you to build a lovely little manifest file (consumed by dtsinstall). Try using that from a continuous build process. Try also using that manifest as an authoritative source and purging anything that isn't on the list. The other option is dtutil which provides all the functionality with no real story for using it. Try using only dtutil to delete nested folders. From the command-line, I should be able to say "use this manifest file and go deploy all of these packages to that SQL server and instead of dumping everything into the base folder, please put them in folder bar, which is a child of Foo." Oh, and to make sure we have information a-plenty for the SOX/FINRA/NASD/etc auditors, this will nicely report all actions similar to the dtsinstall functionality.

History: At my previous engagement, we used the permissions on the file server for controlling deployment. At my current digs, we store the packages in msdb. Not the SSIS package store but actually inside the database (msdb.dbo.sysdtspackages90) and that has presented opportunities for optimization. Currently we just use the manifest file and dtsinstall and that was fine with only one application using it and all of 10 packages. Now we have 5 different applications and 69 pages, it's getting a little ridiculous.

Current status: Given a manifest file, a SQL Server and the destination folder on SQL server, the DeployManifest method will create the folder structure within SQL server. If the leaf folder exists, it will expunge any content in that folder and/or subfolder. It will then attempt to load all the packages into SQL server.

TODO: A proper TODO list will be added but my mental list would be logging and error reporting, validation of uploaded packages, promote the server variable to a member instead of passing it everywhere, ensure consistent nomenclature in code (packagePath, folder vs directory, etc), grab an implementation of getopts to make this a real command-line tool. Also, examine licenses to make sure we have a sane one. Longer term ideas include reworking to allow for all the dtutil options (file & ssis package store plus the options I've not touched like package encryption) as well as exploring how dtutil does it under the sheets to see if SQL Server offers better methods.

Feedback: Interested in joining, think this should be wrapped into a larger project, care to ridicule me for how I've wasted my time by not seeing feature X, want to hire me or any other feedback, sing out.


Last edited Sep 3, 2008 at 2:31 AM by bfellows, version 5