SQL Server 2012 Integration Services - Project Deployment

Friday May 25th 2012 by Marcin Policht

SQL Server 2012 Integration Services parameters introduce a new way of dealing with package development, deployment, and execution. In order to truly appreciate their relevance, it is necessary to take a look at the new Project Deployment Model.

In our recent articles published on this forum, we have been discussing different methods of assigning values to properties of SQL Server 2012 Integration Services packages and their components, focusing in particular on variables and parameters. As we pointed out, the purpose and configuration of variables have not changed significantly since the previous version of the product. On the other hand, parameters introduce a new way of dealing with package development, deployment, and execution. However, in order to truly appreciate their relevance, we need to take a look at each stage of their lifecycle. It is also important to note that parameters are intrisincally tied to the new Project Deployment Model, which is assigned at the time of package creation (although conversion from a package created using Package Deployment Model is also possible).

We already have provided an overview of parameters in the context of package development. Once this stage is completed, the next logical step involves deploying the package to either the file system, SSIS Package Store (both of these options correspond to the legacy Package Deployment Model), or to an SSIS catalog (in the process referred to as Project Deployment. The last one of these three relies on a SQL Server based construct (implemented as a user database with associated CLR stored procedures, accessible via SQL Server Management Studio and named by default SSISDB) and offers a number of advantages over its legacy counterparts. Some of the most relevant ones include the ability to centralize management of parameters and connection managers (via the Configure entry in the context sensitive menu of each project deployed to SSISDB, displayed in the Object Explorer window of SQL Server Management Studio) or the option to assign arbitrary property values at execution, overriding their values provided during design or deployment (including cases where they are not exposed via package or project level parameters). In addition, projects deployed to the SSIS catalog (along with their parameters) can be automatically encrypted. The encryption key is protected with a password you specify when creating the catalog, instead of relying on error-prone, package-based protection levels, used in earlier versions of SSIS and access to them can be individually controlled.

The Integration Services catalog is not automatically created, so deployment attempts from the SQL Server Data Tools will result in an error message stating An Integration Services catalog (SSISDB) was not found on this server instance. To deploy a project to this server, you must create the SSISDB catalog. Open the Create Catalog dialog box from the Integration Services Catalog node. To resolve this issue, launch SQL Server Management Studio and connect to the SQL Server hosting Integration Services instance. In the list of its top level folders, you should be able to locate one labeled Integration Services Catalog. Before deploying packages to it, we need to explicitly invoke its creation and enable Common Language Runtime (CLR) integration. From its context sensitive menu, select the Create Catalog... entry. In the resulting Create Catalog dialog box, mark the Enable CRL integration checkbox (note that you also have the option to Enable automatic execution of Integration Services stored procedures at SQL Server startup) and assign a password that will be used to facilitate encryption of the catalog content (its knowledge is required when moving or migrating the catalog to another SQL Server instance).

Once the SSISDB node appears under Integration Services Catalogs folder, you are ready to deploy projects to it (although you might want to consider first setting up an arbitrary folder hiearchy underneath it, which will help you keep them organized in some logically consistent manner). With our sample project opened in the SQL Server Data Tools interface, select the Deploy item from the Project menu to invoke Integration Services Deployment Wizard. On the Select Source page, you wil be prompted to provide either the Project deployment file (which, in our case, is automatically set to the .ispac file representing the project currently opened in the SQL Server Data Tools interface) or an existing Integration Services catalog (facilitating copying already deployed projects). Accept the default setting (pointing to the local .ispac file) and click on the Next command button. On the Select Destination page, enter the name of the server that hosts the Integration Services catalog and (if desired) the path of the target folder within the catalog. Verify your choices on the Review page, use the Deploy command button to finalize the process and examine the outcome on the Results page.

At this point, the newly deployed project should appear as a node under the autogenerated Projects subfolder within the folder hierarchy of SSISDB catalog (along with the Environments folder, whose purpose we will explore in more detail in upcoming articles). You will also find a Packages subfolder containing the default Package.dtsx entry there. Its context sensitive menu allows you to carry out the following actions:

  • Configure - triggers display of the Configure dialog box, which offers the ability to manage:


    • Parameters - the Parameters tab of the Parameters section of the Configure dialog box provides access to parameters, which, as described in the previous article of our series, are defined during the development stage. The are accessible via the Parameters tab in the Designer window within the SQL Server Data Tools (as well as via the context-sensitive menu Parameterize... entry of tasks, containers, connection managers, and event handlers) and scoped on the package or project level, they operate in a manner equivalent to input parameters of programming language functions and, just like them, can be defined as optional or required. (In the case of the former, the value assigned during design time can be used at runtime, while the latter has to be specified explicitly when the package is invoked).


    • Connection Managers - the Connection Managers tab of the Parameters section of the Configure dialog box provides access to all connection managers included in the package. Effectively, you have the ability to modify package behavior prior to its execution, by changing such parameters as the target server and database or credentials defining connection security context (when dealing with SQL Servers) as well as code page, file name and location, or file format (in case of flat files).


    • References - the References section of the Configure dialog box allows you to take advantage of environments, which facilitate scenarios where a package deployed to SSIS Server needs to be executed with different groups of parameter values. In such cases, you can define collections of variables (effectively forming such environments), which can be used to set package parameters. You have an option of choosing one of these environments when the package is launched (by manipulating the Environment entry in the Execute Package dialog box available via the Execute option in the Package context sensitive menu (to be discussed next). We will discuss environments in more detailed fashion in our upcoming articles.


  • Execute - intended for invoking package execution, provides you (via a tabbed dialog box) with options to set values of Parameters and Connection Managers (for each of them you can also choose the desired Environment). In this case, however, the settings are applicable only to the current invocation and not retained (unlike those assigned from the Configure dialog box). In addition, the Advanced tab offers the ability to override values of package properties (which involves specifying package property using its property path and assigning an arbitrary value to it) and designate the execution logging level (None, Basic, Performance, and Verbose). Finally, you also have access to Dump on errors and 32-bit runtime settings, which allows you to capture content of memory in the case of a runtime error and execute packages in 32-bit mode, respectively. Information about package execution is summarized in the Integration Servies Dashboard report, displayed automatically in the SQL Server Data Tools interface.


  • Validate - gives you the ability to carry out package validation without actually executing the package. As expected, this option provides access to the subset of settings as the Execute task (i.e. arbitrary configuration of Parameters and Connection Managers, as well as the choice of desired Environment), excluding (understandably) those applicable to logging, property overrides, or memory dumps.

Even this brief introduction into the capabilities of Project Deployment Model should give you some sense of the wide range of benefits you can realize by abandoning the legacy approach to deploying SSIS packages. We will continue exploring other capabilities that make such a switch even more compelling in our next article.

See all articles by Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved