I love SSIS Server, it was one of the greatest addition to the SQL Server Integration Services in 2012 version without any doubt, even though the RTM bits were not production ready.
There is one issue with the SSIS Project Deployment that I have had some issues in the past weeks –Â the Project Deployment model. There is no problem with it by default, well as long as you are doing the default things, but once you are in the need of deploying projects under different name than it was originally created – then you are in trouble. :)
In one of my current projects I have a need to deploy regularly a number of SSIS Projects, where some of them were actually created under a different name than they are actually handled in production. So if you are using the wonderful SSIS Deployment Wizard – you are quite fine, because as you know – there you can select the complete path of your project and where you can overwrite any existing project and where you can specify any name for your project – it will simply work.
Imagine that you to increase the project deployment frequency and that the actual number of SSIS Projects goes up and there is no logic to continue manual deployment.
There are a couple of logical paths to look for the solution :
1) T-SQL – Microsoft has implemented quite an impressive integration with the SSIS Server and I simply love it.
2) Powershell – nuff said
:) Do not waste your time, since both of those ways are wrong roads – you simply can not deploy an .Ispac file to your SSIS Server if your are looking to do that under a different name than the one that the project was originally conceived.
Using catalog.deploy_project function will result into an error message if the project name is different from the originally created one.
How on planet Earth could it be ? We can deploy a project (.ispac file) under a different name when using GUI Deployment Wizard but there is no way to automate this ?
What if I can’t recompile the original projects ?
What if I need to deploy a single project under multiple names ? (Yes, in an ideal world everyone would use environments to battle off some of those cases, but in the real world we all know how you can have multiple set of existing rules requiring stuff to be implemented).
Well, actually there is a nice :) solution – as Matt Masson blogged about SSIS Project Deployment , we can actually extract then execution command from the GUI Deployment Wizard, which is actually the very same command but which receives /Silent addition in order to avoid all GUI Screens.
Than we simply change the name of the SSIS Project to whatever is necessary, and here we are – happy deploying SSIS Projects as needed and where needed, without complexity (but also without flexibility) of Powershell.
Here I am deploying my SSIS Project to a different Name while using command line:
ISDeploymentWizard.exe /Silent /SourcePath:"C:\Install\Project.ispac" /DestinationServer:"." /DestinationPath:"/SSISDB/MyBlogPost/MasterProject"