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:
IntSerServ_00
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.
IntSerServ_01
Since you installed with Windows Authentication, just click Connect.
Oops! you may get this message.
IntSerServ_02
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.
IntSerServ_04
Start the SQL Server Integration Services in Control Panel|..|Services shown.
IntSerServ_05
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.
IntSerServ_06
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.
IntSerServ_07
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.
IntSerServ_08
This is an expanded view of the SSISDB in the Object Explorer of the named instance.
IntSerServ_09
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)
No comments:
Post a Comment