Thursday, December 24, 2015

Integration Services Server (SSIS) in SQL Server 2012 and SSISDB

Integration Services Server is basically the SQL Server Database Engine that hosts a special database that does not allow replication or mirroring, the SSISDB. The database stores the following objects:

  • Packages
  • Projects
  • Parameters
  • Permissions
  • Server Properties
  • Operational History
While installing SQL Server 2012you would have installed SQL Server Integration Services as shown here:


SSISDB provides the public views that allows querying and for manageability, it allows creating stored procedures. SSISDB has to be in place before you deploy SSIS Projects.

The Packages are created using SQL Server Data Tools and deployed to SSIS. It assumes you have created the SSISDB before deploying the SSIS Projects.

Where is the SSISDB or how do you create the database SSISDB?

Assuming you have installed SQL Server 2012 as I the previously mentioned link, you launch the SQL Server Management Studio and change over from connecting to Database Engine to Integration Services  as shown.


Since you installed with Windows Authentication, just click Connect.

Oops! you may get this message.


Now go ahead and launch SQL Server Management Studio with Elevated permissions (As administrator).  Repeat connecting to Integration Services as before. You may get this message if SQL Server Integration Services has not started.


Start the SQL Server Integration Services in Control Panel|..|Services  shown.


Click Start to start the SQL Server Integration Services 11.0. It processes the information and status changes to Running.

Now connect to the Integration Services as before (after launching the SSMS in Administrative mode).

The first of the nodes in the Object Explorer is the Integration Services. This is an expanded view of the Integration Services Server.

This is a named instance of SQL Server that will host the SSISDB.


Creating the SSISDB

Connect to the named instance of SQL Server 2012, herein the Hodentek8\RegencyPark.
Right click the Integration Servies Catalogs and click Create Catalog.. from the drop-down menu as shown above. The following window will be displayed.


Place check mark for Enable CLR Integration. Leave the catalog database name as is and create a password to protect data using encryption (enter and retype password). Save this information in a secure place (under lock and key). Click OK.

Now the SSISDB gets created as shown.

This is an expanded view of the SSISDB in the Object Explorer of the named instance.


In the next post a SSIS Project creation will be described that can be deployed to the Integration Services Server.

Get jump start on SSIS (the version is old but concepts are same)

Read here:

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...