In our recent articles we have been discussing different methods of facilitating code reusability in SQL Server 2012 Integration Services. So far, we have demonstrated how to leverage variables (which have been available in earlier versions of the product) as well as newly introduced parameters to accomplish this objective. Now we will extend the scope of our discussion by introducing package and project configurations, which offer different ways of modifying values of variables and parameters without having to directly edit content of the packages and projects they are part of.
Let’s start by pointing out that the meaning of the term configuration varies depending on the context (package vs. project and solution). In addition, starting with SSIS 2012, the use of package configuration is intended only for legacy Package Deployment Model and is disabled by default in the new Project Deployment Model (which is becoming the recommended deployment methodology, due to a number of benefits it delivers).
In the traditional package deployment scenario, configurations leverage the mechanism that has been introduced in SSIS 2005 (based on functionality incorporated into Dynamic Properties Task in SQL Server 2000 Data Transformation Services), which allows changes to package components (including their properties and variables) using external data sources (such as XMLfiles, registry entries, database tables, environment variables, or parent package variables). This, effectively, gives you the ability to alter package content in a relatively unintrusive manner, by simply modifying one (or more) of these designated data stores. Note that resulting configuration changes are applied during package load (when package execution is invoked), rather than dynamically at runtime (with the exception of those based on parent package variables).
To implement a package configuration in SSIS 2012, launch SQL Server Data Tools and open an existing project stored in the package deployment model format (or create a new project and convert it to the legacy model). With the package open and active in the Designer workspace, select the Package Configurations… item from the SSIS top level menu (alternatively, you can locate it in the context-sensitive menu of the Designer surface). In the resulting Package Configurations Organizer window (whose layout implies that it is possible to have multiple configurations within a single package), turn on the Enable package configurations checkbox and click on the Add… command button. This will trigger Package Configuration Wizard, which guides you through the process of creating a configuration. On the Select Configuration Type page, you need to either point directly to one of five available store types (including XML configuration file, Environment variable, Registry entry, Parent package variable, and SQL Server) or choose an environment variable that will contain the location of a configuration store.
Once you have chosen the configuration type and advanced to the next step of the wizard, the resulting interface will depend on the option you selected. If you decided to use a direct assignment, then you will be prompted to select a single property or variable (when working with Registry entry, Parent package variable, or Environment variable) or multiple ones (when dealing with XML configuration file and SQL Server). If you decided to use indirect assignment, no further steps are required (instead, you simply need to specify the configuration name and confirm your selection by clicking on the Finish button on the Completing the Wizard page).
While this approach provides a mechanism to control values of arbitrary variables and properties by manipulating external data stores, it does not fully integrate with the SSIS software design paradigm. In particular, consider a lifecycle of your SSIS projects. Typically, you would target different environments (and the data stores associated with them) during development, testing, and production stages. Visual Studio includes provisions that are intended to reflect this process. To view them, display the Properties dialog box of your project (from the corresponding item in the context sensitive menu of the top level node in the Solution Explorer window in SQL Server Data Tools). You will find the Configuration Properties node with three subnodes labeled Build, Deployment, and Debugging there, which give you the ability to set attributes such as debug InteractiveMode (that you would most likely enable during development, but disable in production), CmdLineArguments (allowing you to modify runtime behavior), or AllowConfigurationChanges (indicating whether package configurations can be changed during deployment). All of them can be independently set for each configuration you create. However, these are solution configurations, which are managed independently (by clicking on the Configuration Manager… command button in the project’s Property dialog box) of the package configurations we discussed earlier. Effectively, if you want to switch between development, test, and production environments, you need to manage both types of configurations.
Such overhead is eliminated when using Project Deployment Model(as we pointed out earlier, the new approach also provides the ability to centralize management of parameters and connection managers as well as assign arbitrary property values at execution, overriding their values provided during design or deployment – including those not exposed via package or project level parameters). To illustrate this, let’s examine the properties of a sample project that leverages the new deployment model.
To create the project, launch SQL Server Data Tools. Next, on the Start Page of the Visual Studio 2010 Shell, click on New Project… link, navigate to the Integration Services node in the Installed Templates pane, and pick Integration Services Project. In the Name textbox, type in an arbitrary name of the new project, specify its Location, and uncheck the Create directory for solution checkbox.
In our example, we will perform export from the dbo.FactCurrencyRate table residing in the AdventureWorksDW database to a text file, so we will need two connection managers (one for the SQL Server and the other for the text file) and a single Data Flow task. The format of the text file will match the structure of the table, with rows divided into four columns – AverageRate, CurrencyKey, Date, and EndOfDayRate. To implement it, drag the Data Flow Task from the Common section of SSIS Toolbox onto the design area of the Control Flow tab or switch to Data Flow tab and use the hyperlink in the center of its window stating, No Data Flow tasks have been added to this package. Click here to add a new Data Flow task. In the Favorites section of SSIS Toolbox, you will find Source Assistant. Drag and drop it onto the Designer area to launch the Add New Source window. Select SQL Server as the source type and click on New… in the Select connection managers listbox. This will trigger the Configure OLE DB Connection Manager dialog box, where you need to designate the SQL Server name (localhost or simply . will suffice in our case), the authentication method (Windows or SQL), and the target database (AdventureWorksDW). Once you confirm your choices by clicking on the OK command button, a rectangle representing OLE DB Source will appear on the Designer pane (note that the corresponding OLE DB Connection manager is also automatically created). As the red cross mark on its side indicates, you need to provide the name of a table or view in order for its configuration to be complete. To accomplish this, right click on it and select Edit… from the context sensitive menu. In the resulting OLE DB Source Editor menu, set [dbo].[FactCurrencyRate] in the Name of the table or view listbox, switch to the Columns section, and clear the checkbox next to the DateKey entry (since we decided to exclude it from the exported data set). Switch back to the Connection Manager section, use the Preview… command button to verify that your settings are correct, and finalize their configuration by clicking on the OK command button.
We are ready at this point to set up the target of data export. From the Other Destinations section in the SSIS Toolbox pick the Flat File Destination and drop it onto the Designer area directly below the OLE DB Source. Extend the green arrow originating from its bottom edge (and representing data flow) until both components are connected. Next, invoke the Flat File Destination Editor (using Edit… entry in its context-sensitive menu). In the Connection Manager section, click on the New… command button to create a new Flat File connection manager. Pick the desired file format (Delimited, Fixed width, Fixed width with row delimiters, or Ragged right). Next, in the General section of the editor, specify the connection manager’s name and description, as well as the file location and format, including such parameters as text qualifier, header row delimiter, and header rows to skip. In the Columns section, specify rows and column delimiters. The Advanced section offers options to alter individual column names and automatically assigned data types. Once you are back in the main Editor window, you have access to Overwrite data in the file checkbox and Mappings section, where you change default association between input (from the OLE DB Data Source) and destination (recorded in the text file) columns.
For the sake of simplicity, we will create only two parameters representing connection strings of the two connection managers included in the project. This can be done by invoking the Parameterize… option from the context-sensitive menu of each of them. Once the Parameterize dialog box is displayed, select the ConnectionString entry in the Property: listbox, ensure that Use existing parameter option is selected, and click on OK to finalize your choices. If you switch to the Parameters tab in the Designer interface, you should see both parameters listed, along with their values, data types, descriptions, as well as Sensitive and Required settings.
Since our intention is to customize individual parameters (connection strings) depending on solution configurations, let’s create additional configurations first. Use the context sensitive menu of the top-level node in Solution Explorer to display the Properties dialog box of our solution. Click on the command button in the upper right corner to launch Configuration Manager. In the Active Solution configuration listbox, use New… option to create Test and Production solution configurations (note that Deployment section of the Configuration Properties node allows you to designate the target Server Name and Server Project Path).
At this point, you can use the right-most icon in the toolbar of the Parameters tab of the package Designer interface to associate values of individual parameters with solution configurations. Once you click on it, you will be presented with the Manage Parameter Values – Package dialog box, containing four columns. The first one labeled Parameters contains names of parameters you added to configurations, while the remaining ones will match the names of configurations you created (in our case, Development, Production, and Test). For each of them, you can set a different value that is appropriate for each respective environment. To switch between them, use the Configuration Manager window (to designate the active solution configuration) or simply select the desired one from the listbox appearing in the middle of Standard toolbar in the SQL Server Data Tools window. This functionality clearly simplifies management of SSIS project lifecycle.