Creating the SSIS Catalog

In SSIS in SQL Server 2012, it is no longer necessary (for new installations/packages) to install a separate integration services instance onto the server. It can all be managed from within the database engine. In order to be able to utilize this feature, you’ll need to create an SSIS catalog. 

When you connect to the database engine instance of a SQL Server 2012 instance, the last folder before SQL Server Agent is a folder called “integration services catalogs”

image

 

Right click, and select “Create Catalog”. A screen like the below will appear:

image

 

Now, in order for the catalog to even work, CLR integration has to be enabled. Why it’s optional here is beyond me, but check the box and the rest of the screen will activate:

Check if you want to enable automatic execution of Integration Services stored procedures at SQL startup, and create an encryption password that you’ll use to encrypt the contents of the database. Remember that this is going to house all of your actual packages, so if they’re doing some sensitive stuff for your business make sure you create a strong password. password is probably not a good idea….:)

Once you create a password and click ok, it will think for a few minutes and then create a IS catalog called “SSISDB”. In this release, you cannot rename it, you are stuck with “SSISDB”. Another item to note about this release is since you can’t rename the catalog db you just created (SSISDB), you can only create 1 catalog db per instance. If you try anyways, you’ll get an error message that says that a catalog already exists, and there is currently only 1 catalog supported.

image

 

If you expand the SSISDB node, you won’t see anything at first. The next step you have to do is create the actual folders where the packages will live and you can deploy them to. Right click, and select “Create Folder”

image

You’ll get a really straight-forward and simple page asking to create a folder name and description, and then click ok.

image

 

After you’re done, you’ll have a new folder in your list with 2 nodes underneath it. Projects and environments. Projects is where you will deploy your packages too, and environments are where you can specify the different environment to run the package against (dev, qa, production, etc). A lot different from previous versions of SSIS but hopefully much simpler!

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s