Before you can deploy your Projects to the SSIS Server , you’ll need to create a new SSIS Catalog through SSMS.
Connect to your SQL Server instance (local or remote), right click on the Integration Services node, and select Create Catalog. This will launch the catalog creation dialog.
A lot of the SSIS server functionality is based on SQL CLR, so enabling CLR integration is required to create the SSIS Catalog. You are also prompted for a password which is used during the creation of the database master key (the catalog uses standard encryption to protect sensitive values in parameters and packages – please see the Encryption Hierarchy post in BOL for more information).
When the second checkbox – “Enable automatic execution of Integration Services stored procedures at SQL Server startup” – is checked, an SSIS cleanup job will be executed each time the server instance is restarted. This cleanup job will fix the status of any packages that were running at the time the instance went down.
Once the catalog is created, you will see a new user database (SSISDB), and an SSISDB node under the Integration Services node in the SSMS Object Explorer. From there, you can create Folders while you public your SSIS project to.
Right clicking on a Project gives you a number of management options:
Configure
This UI allows you to set default values for project and package parameters, and connection managers. The scope drop down lets you filter the list of parameters down to Entry Point packages (which is a flag that can be set on the package when it is created in BIDS), or down to individual packages in the project. The references tab allows you to associate this project with server environments defined on this server.
This option is also available when you right click on a single package – the only difference is the list of parameters will automatically filtered to the current package.
Validate
Allows you to validate packages on the server. The option appears both at the Project level, and from the right-click menu of individual packages. When launched from the Project level, all packages in the project will be validated.
Move
This UI allows you to move the Project to a new folder in the SSIS Catalog. Note, you can use the SSIS Deployment Wizard to move projects between servers.
Versions
The SSIS Catalog allows you to roll back to previous versions of the project.
The server will maintain 10 versions of your project by default, but you can configure this setting on the Catalog Properties page.
Running Packages
There are three main ways to run packages stored in the SSIS Catalog through SSMS.
- Interactive Execution
- SQL Agent
- T-SQL
Interactive Execution
Through SSMS, you can right click a package and select Run. This will launch a package execution on the server, using your user credentials. The run package UI lets you set parameter values (overriding whatever defaults were set in the package at design time, or set on the server), and change values for connection managers.
SQL Agent
The Integration Services job step for SQL Agent has been updated in CTP3 to support execution of packages from an SSIS Catalog. The UI is very similar to the one you get for interactive package execution. Just like in previous versions, you can choose to run the package as the SQL Agent service account, or a proxy account.
Note: we found a last minute bug in the SQL Agent UI that prevents it from showing any package parameters (or connection managers). It is outlined in the release notes .
Using T-SQL to Run Packages
All of the SSIS functionality exposed in SSMS is (and more!) is also exposed through a T-SQL API. We expose a set of public views and stored procedures (in the catalog schema), as well as some private tables and stored procedures meant for internal use (in the internal schema).
You need to call two stored procedures to launch a package via T-SQL: catalog.create_exectution , which gives you an @execution_id , and catalog.start_execution , which starts that execution. The execution is created in two steps, because you might want to set parameter values (catalog.set_execution_parameter_value), or set up one or more data taps ( catalog.add_data_tap , which will be covered in a later blog post).
Conclusion
This post touches on some of the new SSIS management functionality in SQL Server Denali. We’ll continue to post more about these new features (such as the reporting, troubleshooting and logging, and using “environments”) as the 30 Days of SSIS blog series continues.