Create the SSIS Catalog
After you design and test packages in SQL Server Data Tools, you can deploy the projects that contain the packages to an Integration Services server. Before you can deploy the projects to the Integration Services server, the server must contain the SSISDB catalog. The installation program for SQL Server 2012 does not automatically create the catalog; you need to manually create the catalog by using the following instructions.
You can create the SSISDB catalog in SQL Server Management Studio. You also create the catalog programmatically by using Windows PowerShell and the Microsoft.SqlServer.Management.IntegrationServices namespace. In the following Windows PowerShell example, the MaxProjectVersions and OperationLogRentionTime properties are modified.
# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
Write-Host "Connecting to server ..."
# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
# Get the existing SSIS Catalog
$catalog = $integrationServices.Catalogs["SSISDB"]
# Changes the catalog property - Maximum number of versions maintained for a project
$catalog.MaxProjectVersions = 5
$catalog.Alter()
# Changes the retention window
$catalog.OperationLogRetentionTime = 100
$catalog.Alter()
For more examples of how to use Windows PowerShell and the Microsoft.SqlServer.Management.IntegrationServices namespace, see the blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com. For an overview of the namespace and code examples, see the blog entry, A Glimpse of the SSIS Catalog Managed Object Model, on blogs.msdn.com.
To create the SSISDB catalog in SQL Server Management Studio
-
Open SQL Server Management Studio.
-
Connect to the SQL Server Database Engine.
-
In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
-
Click Enable CLR Integration.
The catalog uses CLR stored procedures.
-
Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted.
The stored procedure performs maintenance of the state of operations for the SSISDB catalog. It fixes the status of any packages there were running if and when the SSIS server instance goes down.
-
Enter a password, and then click Ok.
The password protects the database master key that is used for encrypting the catalog data. Save the password in a secure location. It is recommended that you also back up the database master key. For more information, see Back Up a Database Master Key.
