In one of our recent articles published on this forum, we have described how variables can be used to simplify development and maintenance of SQL Server 2012 Integration Services packages and projects. As we have pointed out, variables serve a dual role. Their primary purpose is to provide runtime storage for values utilized by SSIS components, such as containers, tasks, event handlers, precedence constraints, loops, and stored procedures, as well as the packages themselves. At the same time, they also facilitate adjusting these values without modifying package content (which would otherwise require additional programming effort, with obvious time and cost implications). In SQL Server 2012, Microsoft introduced another approach to accommodate the dynamic nature of SSIS constructs in the form of package and project parameters, which will be the subject of this presentation.
This innovation is directly related to changes in SSIS development, deployment, and execution model, which closely integrates a project with the packages contained within it. While Business Intelligence Development Studio did enforce a similar paradigm, the degree of relationship between these entities was hardly relevant. SQL Server Data Tools on the other hand, provides the ability to combine multi-package projects into a single unit (taking the form of a file with extension .ispac). This eliminates the possibility of breaking dependencies between parent and child packages during subsequent deployments. New projects created using SQL Server Data Tools are by default based on the Project Deployment Model, which unlike the legacy Package Deployment Model, yields a single deployment unit and offers a variety of benefits, including the ability to employ parameters (as well as other features, such as environments, environment variables, and environment references).
Parameters can be considered as a feature intended to supplement configurations, which were introduced in SSIS 2005, in order to allow changes to package components (including their properties and variables) based on the content of external data sources (such as XML files, registry entries, database tables, environment variables, or parent package variables). Changes to configurations (with the exception of those based on parent package variables) are not applied dynamically (during runtime), but instead are introduced during package load (when package execution is invoked).
In principle, Parameters can be viewed as read-only variables, scoped on the or project level (i.e. accessible either by the package and all of its components or by all packages within that project). Effectively, 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 run). They can be supplied when launching packages via an Execute Package task or a SQL Server Agent job (as the SQL Server Integration Services Package step). You can easily review all of the package-level parameters by referring to the Parameters tab of the Designer window. A similar listing of the project-level parameters is available via the context-sensitive menu of the Project.params node in Solution Explorer. As far as their configuration is concerned, the easiest way to accomplish it is by using the new context sensitive menu option (labeled Parameterize…) of tasks, variables, and connection managers.
Let’s see how this concept can be applied to a sample package to eliminate some of its hard-coded values and how this approach compares to the one involving SSIS variables. To create it, launch SQL Server Data Tools (from the Microsoft SQL Server 2012 group in the Start->Programs menu). 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 texbox, type in an arbitrary name of the new project, specify its Location, and uncheck the Create directory for solution checkbox.
Our example will be loosely based on the design described in SSIS Tutorial available in the MSDN Library. For the sake of simplicity, we will perform export from the dbo.FactCurrencyRate table, residing in the AdventureWorksDW database, to a text file (rather than importing its content into the database). Note that regardless of the direction of data transfer, we 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 (we will ignore the DateKey in our export).
While the sequence in which these components are added is, for the most part arbitrary, the process can be simplified by creating and configuring the Data Flow task first. To accomplish this, either drag it from the Common section of SSIS Toolbox onto the design area of the Control Flow tab or switch to the 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. Note that the content of the SSIS Toolbox will reflect the new context (when switching between the Control Flow and Data Flow tabs). In its top Favorites section, 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 (AdventureWorksDWDenali). 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 the 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 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 (you also have the ability to indicate whether column names are included in the first data row). In the Columns section, specify rows and column delimiters (if they are different from defaults). The Advanced section offers options to alter individual column names and automatically assigned data types (the latter has limited relevance in our case, considering that we are using a text file as the destination). A new feature exposed by the Suggest Types… command button, identifies the most appropriate one based on the sample size you specify. Once you are back in the main Editor window, you have access to Overwrite data in the file checkbox (enabled by default) and Mappings section, where you change the default association between input (from the OLE DB Data Source) and destination (recorded in the text file) columns.
As you might recall, when discussing use of SSIS variables, we used them to express the location and name of the target text file. We also applied a SELECT statement to limit the scope of the extracted data to entries where the value of the CurrencyKey field was equal to 3 (relying for this purpose on an expression). Let’s examine whether we can accomplish the same objectives by employing parameters.
As mentioned earlier, you can assign parameters to properties of SSIS components via their Parameterize… menu entry. Invoking this action for our Flat File Connection Manager triggers display the Parameterize dialog box. From this interface, you have the option of designating a property (we are interested in ConnectionString), which value will be set using an existing or a newly created parameter (whose default name is formed by concatenating the scope, component, and property names, separated by the double-colon and underscore, resulting, in our case, in $Package::FlatFileConnectionManager_ConnectionString), setting its value and scope (package vs. project), as well as marking it as required (sensitive option is in this case unavailable). Note that the parameter is implemented in the form of expression (@{$Package::FlatFileConnectionManager_ConnectionString}, which you can easily verify by checking the Properties window of the connection manager).
Unfortunately the same functionality is not available in the case of OLE DB Source (Parameterize… entry appears in the context-sensitive menus of Control Flow tasks, containers, event handlers, and connection managers, but not individual Data Flow components). While you will find the Parameters… command button in the Connection Manager section of the OLE DB Source Editor window, this is intended for parameterized SQL commands and is conceptually and functionally different from the package and project-level parameters. As a matter of fact, parameters in this context are mapped to variables, so they are more relevant to the topics discussed in our previous article, rather than this one. (Incidentally, using the Preview… command button with the query parameter in place might trigger an error message complaining about No value given for one or more required parameters, even in situations where the package executes successfully).
In conclusion, even with the introduction of new features in SQL Server 2012 Integration Services, variables still serve an important role in package development and maintenance. While the functionality delivered by parameters is also important, providing us with a single global view of read-only values across all packages in the project (as well as within a package, somewhat overlapping with package-level variables), in order to truly appreciate their value we need to first review configurations as well as new ways to deploy and store projects, which will be covered in our next article.