August 21, 2017 by Kenneth Fisher
You’ve installed SSDT, created an SSIS project, created any packages you need and now you want to place that project and its packages onto the SQL instance so you can run them. FYI there are several ways to do this using package or project deployment, I’m just going to talk about one specific method using project deployment.
The first thing to do is create the SSIS catalog on your instance (if it doesn’t already exist) and of course, open up your project.
I’ve created a very simple package that just exports the employee table from AdventureWorks2014 to a CSV just so I have something in my project.
Build -> Build Solution
And an ispac file is built. Assuming everything went well you’ll see the path at the bottom of the Ouput window.
Take that path and find the file.
Now the interesting thing is that an ispac file is just a zip file. If you rename it to .zip and open it you can see what’s inside it. Including the .dtsx file(s) for the packages.
In my case ispac wasn’t mapped to the correct program so I had to manually associate the extension with the correct program. In this case that is ISDeploymentWizard.exe. It was located in C:\Program Files\Microsoft SQL Server\130\DTS\Binn on my machine. Of course, in my case for some reason the mapping didn’t work properly and I kept getting the command line (yes it appears you can run this via command line) help screen instead of the GUI. So I have to open the deployment wizard another way. I just did a quick search on Deployment Wizard and opened it manually. Note: You can open it directly through an SSMS connection to integration services, or SSDT in the project (Build -> Deploy) which would avoid the whole need to create the ispac file at all. But in this case, I’m assuming we were just given an ispac file to deploy.
Now we fill out the wizard.
(If the double click thing worked then the source path is already filled in.
If you just created your SSISDB catalog then you’re going to have to create a folder to store your project in.
Now review what you’ve done (yeah, I don’t usually pay much attention to this page either). One useful thing you’ll notice on this page is that at the top of the summary the command line parameters for ISDeploymentWizard.exe are displayed in case you want to automate the process. Finally we hit Deploy.
Yay! It’s deployed! And we can now see it in SSISDB!