Maximizing SQL Server Integration Services: Optimizing the SSIS Project Layout

In a series of blogs about the design and implementation of solutions using SQL Server Integration Services (SSIS), Part 1 addressed design tasks and whether or not to stage the data in an intermediate database or operational data store. This may be necessary if the data is being pulled from a number of different data sources, or if any of the sources are transactional systems that don’t “like” to be hit a lot by processes outside of their own application(s). Making this decision should not be taken lightly, as the number of tables and other sets of data to copy could be quite large, depending on your project.

This second blog about SSIS addresses some techniques you can employ with SQL Services Integration Services to reduce the required development time.

Introduced in SQL Server 2012, Integration Services offers two deployment models: the traditional package deployment, as used in SQL Server 2005 and 2008, and a “new” project deployment. With the project deployment model, all objects in the SSIS project can be saved to the Integration Services Catalog. Besides the packages themselves, connection managers and project-level parameters are included in these deployable components.

Project Parameters

If you were a developer back in the SSIS 2005 and 2008 days and felt betrayed by Microsoft with the complexity around parameters files (or was that just me?), Microsoft has greatly improved the functionality with Project Parameters. It allows for a simpler way to define variables and a way to use them within the packages contained in the same project. More importantly, when the project is deployed to a server, the parameters can be changed in the SQL Server Management Studio (Management Studio or SSMS), instead of having to track down and update an XML. These parameters are stored in the file called Project.params, as shown in the screenshot to the right.

Below, three variables have been defined for each of the servers involved with this project.

Project-level Connection Managers

As the name implies, connection managers defined in the Solution Explorer are available to all packages in the project. Technically, the connection managers are aliased in each package in the project, so the developer doesn’t need to add anything. Individual packages may still have their own connection managers to unique sources that do not require sharing across the project.

Having added parameters to the project for our servers to go along with their respective connection managers, the connection managers can be updated to use the parameters. There are a couple advantages that come to mind by setting up the project in this manner:

• Multiple developers and multiple development servers – if you are a part of a development team, and each developer has their own development server, each developer only needs to change the project-level parameters to point to their system. As long as the developer doesn’t check the project.params file into source control, the other developers won’t be impacted by having to reset connection strings.
• Deployment – When the project is deployed to a test or production server, the person managing the deployment only needs to change the server names in the Project Parameters. This does require a one-time mapping of the parameters to the connection managers within SSMS. Fortunately, subsequent deployments won’t require this step, unless a new connection manager is added.

To change a connection manager to use the server name from a parameter, create a new SSIS package, which will serve as our skeleton, or template, for other packages. In the new package, single-click on the connection manager to update and go to the properties tab (generally in the same area as Solution Explorer). In the “Misc” section should be a property called Expressions. Click on the ellipse (…) on the value side of the pane.  In the Property Expressions Editor, select the drop-down list under Property and choose ServerName and click the ellipse to the right of the Expression.

Within the Expression Builder, find the appropriate Project Parameter to assign to this connection manager and drag the parameter into the Expression. To be sure I had entered the server names correctly, I evaluated the Expression to see the server name.

Click OK to work your way out of the dialogs and repeat for each of the connection managers you have defined. As you can see in the updated screenshot of connection managers, CN_ODS now has the formula symbol, fx, prefixed to the name. This is similar to other places in SSIS you may have previously set Expressions (we will see them again later in this blog series).

With this work complete, it’s time to build out the shell of the SSIS template. Be sure to read all the blogs in the SQL Server Integration Services (SSIS) series, including Part 1 about design tasks and where to stage data.

Click here to read Part 3 – Build a Skeleton SSIS Staging Package

Or, here to reread Part 1 – Maximizing SQL Server Integration Services: Technical Design