SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSIS Project Deployment under different name

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).

SSIS Project Deployment - Select SourceSSIS Project Deployment - Select Destination

SSIS Project Deployment ReviewWell, 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"

Niko's blog

Niko Neugebauer is Microsoft Data Platform professional. A SQL Server MVP, he is very passionate about Data Platform (with a focus on Columnstore) and Community. He is a founder and a leader of the Portuguese SQL Server User Group, SQLPort and a co-founder of 2 more Data Platform User Groups in his home country. His passion for community led him to organise the first SQLSaturday outside of North America – #78 Portuga. Niko presented at a number of different conferences, including PASS Summit, SQLRallys, SQLBits, and SQLSaturdays around the world.

Comments

Leave a comment on the original post [www.nikoport.com, opens in a new window]

Loading comments...