Disclaimer: This blog was previously posted to Superior Consulting Services and has been posted again to this site.
Optimizing the SSIS Project Layout
In the first part of this series, a design task that needs to be addressed is whether or not to stage data in an intermediate database or operational data store. This may be necessary if data is being pulled together 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 its 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. However, there are 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.
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 a simpler way to define variables and 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 (aka Management Studio or SSMS), instead of having to track down an XML and updating it. These parameters are stored in the file called Project.params, as shown in the screenshot to the right. Below, 3 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, having to reset connection string.
- 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 like other places in SSIS you may have set expressions on previously (and we will see them again in this blog series).
With this work complete, it’s time to build out the shell of the SSIS template.