Deploy an SSIS project to the SSIS catalog.


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.

Now try double clicking on the ispac file.

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!

One thought on “Deploy an SSIS project to the SSIS catalog.

  1. Henry says:

    How do i build the ispac file on the command prompt?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: