A few months back, after resuming SSIS development full-time on a new team, I began to look for ways to incrementally improve the processes causing pain around deployments. New or improved packages would be sent to the DBA in the form of an ISPAC and then he’d open SSMS and deploy to the target server using the context menu on the catalog.
This had a few challenges and opportunities for improvement. We support both SQL 2012 & 2014, so it is critical to use the right Management Studio for the job, otherwise the PackageFormatVersion is incorrect. In addition, I have a preference for using scripted deploys, taking the GUI and (some) of the human element out altogether.
I began researching solutions and very quickly remembered that ISDeploymentWizard.exe had a silent mode. In fact it reminds us about this every time we deploy!
I began using ISDeploymentWizard.exe in my releases to Test and Production environments and had some quick success by calling it in a batch file for the DBA to execute. This worked great until I tried to reproduce the process with other team members. I found that people had several versions of SSMS and BIDS/Data Tools/Visual Studio installed and that moving ISDeploymentWizard.exe around to these different machines got mixed results due to varying versions of the DLL dependencies, such as Microsoft.SqlServer.ManagedDTS. They also needed SQL Data Tools or Developer Edition to have the functionality at all. When trying to run ISDeploymentWizard.exe on a machine without all the dependencies, an error window like this pops up:
I wanted something easy to distribute to all the team members and simple to use. I also wanted it to work on a vanilla install of Windows, so that the DBA could run the process on a deploy machine that didn’t necessarily need a SQL Server install. This ruled out the PowerShell solutions that reference a non-redistributable DLL. Eliminating this dependency would also make things easier in the future on a build agent in an automated process.
As I wanted to share this as a free and open source app, I wrote a ISDeploymentCmd, a C# console app on my own time over a weekend. It’s a wrapper around the public SSIS Catalog Stored Procedures that supports the same command line arguments as ISDeploymentWizard.exe. It also adds some additional capabilities, such as logging output. Now, with a simple find/replace in my previously mentioned batch file scripts, I’ve swapped in this tool and had success both in the developer team and in the integration to other environments. At this point it has now been used all the way to Production successfully several times by multiple teams.
Feel free to check out the project on GitHub and grab a copy of the executable on the releases page to try out yourself.