We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers, etc... So do we really need to create a package from scratch each time and add all these commonly used components in each package again and again. Is it possible to create a SSIS package with a basic structure/workflow and common components which can be used as template to create subsequent packages?
SSIS allows creating a package with a basic structure/workflow that has common components which can be further leveraged as a template for reusability or for creating new packages based on this template. This means you are not required to add each item over and over again for each package that you create. This reduces the time needed for development and makes packages look/behave consistently.
Creating a Package to be used as Template
To create a SSIS package to be used as template you have to follow the same approach as creating a new package. You need to use Business Intelligence Development Studio (BIDS) to create a new project of type "Integration Services Project". Create a new package, specify an appropriate name for this package and add the work flow and components you want to be part of the template.
For example, I have a sample package below in which the first task logs the start of the ETL batch. Next I have a container which will eventually contain components for loading data into staging. After that I have another container which will contain components for data loading into dimensions and facts and for cube processing. At the end, it will log success or failure for the package.
Once you are done with creating the basic structure of the package and have added the common components, you need to save a copy of this package at the following locations based on the version of SQL Server you are using:
For SQL Server 2005 <<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems OR <<Installation drive>>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
For SQL Server 2008 <<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems OR <<Installation drive>>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
For SQL Server 2012 <<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems OR <<Installation drive>>:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
You need to specify the drive location where Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) has been deployed. Please note, as BIDS or SSDT runs locally on client machine, you need to copy the template package to the above location on all the development machines you want it to use. For this example we are naming the template package "SamplePackageTemplate.dtsx".
You are not restricted to deploying only one template. You can deploy as many templates as you want to the folders listed above and reuse them as needed.
Using the SSIS Template in Other Projects
In a new or existing project where you want to add this SSIS package template, you just need to right click on the project name in the Solution Explorer, click on Add > New Item as shown below:
In the Add New Item dialog box, you will notice the deployed package template as shown below. You can select it and specify a name for the package for which the template will be used and click on the Add button to add the new package to your project based on the selected template. That's all you have to do. You now have a package that is pre-configured and you can now customize it for your specific need. Please note, the modifications that are done in the new package do not impact the deployed template as we are working with a copy of the template which is now part of the current project and not the template itself.
If you are using SQL Server 2012, when you add a new item you will see the template appearing in the Add New Item dialog box as shown below. Select the template and specify the name for the new package which will be based on this template.
Generate Unique ID
If you are using SQL Server 2005 or 2008 then you should generate a unique ID. This is recommended because it helps in analyzing log data with better differentiation for each package. To generate a unique ID value for the package, click the ID in the Properties pane, and then click Generate New ID.
In SQL Server 2012 when you add a package based on a template, SSDT generates a unique ID for each package and hence you don't need to do it separately.
in this demo, actually the template was not used. it is only a empty shell. moreover, each BI project feed Dimensions and fact tables in the different prespective. however, in this demo assimption was feeding Dim. first and then fact tables. finally, in real BI project staging tables are the major key points, which were missed in this empty shell.
Thanks again for your article.
Wednesday, June 24, 2015 - 8:51:27 AM - Tushar Verma
Im new in SSIS and my company is already making me to re-design everything which has been badly designed. I always strive for standards so that the next developer can find everything in place and documented. Thanks once again.
Resetting the package GUID from a template is absolutely a requirement, but you really need to reset the GUIDs for every task, connection, and other object in the package. The easy way to do this is to install the free BIDS Helper utility (http://bidshelper.codeplex.com), which has a "Reset GUIDs" action that will replace them all at once.
A template should also include whatever logging and error handling components are standard in your organization.
Any copied package (whether from a template or any Add Existing Package action) will include all the connection managers, variables, configuration specifications of the original. But XML configuration files are not copied with the template. If the XML files are in a common location, such as a network share, they will work in the new template-derived package. But if you keep the XML files locally with your packages, you'll have to manually copy the file (or "Add existing item") into the project.
I like to use SQL Server configurations, so all configuration works no matter where on the network I want to deploy or develop the packages.