SSIS - Standard Reusable Templates

SSIS is a well-defined ETL platform to load the raw data from the various OLTP or OLAP sources into an established destination. SSIS provides pretty much all the tools that system need to get the job done but sometimes it’s not always easy to know what tools to use and how to use them. To avoid the different approaches, Standard Reusable Templates are the best way to follow the same standard to accomplish the development jobs.

An ideal SSIS package can contain items such as connection managers, log providers, control flow elements, dataflow elements, event handlers, variables, and configurations files. SSIS template packages are the re-usable packages that one can use in any SSIS project any number of times to maintain the development standards.

System can reuse these items when system uses a standard package template to create a new package. An ideal package template must have the following items:   

1. Connection Managers and Log Providers: Connection manager and a log provider should be the common things for the ideal package template because all event information is logged in the same SQL Server database. 
    
   
   
   
2. Common Variables and Configurations: There are some very common variables which could be used in most of the packages and system should have the same types of configurations settings or files.
   
3. Common Execute SQL Tasks: An ideal package template should Execute SQL task controls to do truncate the data tables or attaches databases. This task is also responsible for deleting the target table of system import process. Therefore the SQL statement has to be updated, so the proper table will be deleted. 
   
4. Common Script Task: It will be included (with all of the same properties and code) in all packages created from that template.
5. Event Handlers: System may need to use same event handlers or error handling across ETL packages in a project. So, this should be the important part of the ideal package templates.
   
6. Send Mail tasks: An ideal system should have a Send Mail task also in the package template that contains an SMTP connection manager and a property expression to build the Subject line. By using the task, package is capable to send the error alerts to the respective authorities.
7. Common Task: In the many projects where many tasks are common across 70% packages e.g. Execute SQL Task, Data Flow elements, and Control Flow elements. It may differ project to project but it is always good idea to encapsulate the common task in a template and use it wherever required.
   

Implement SSIS Template Package: There are very basic steps to create a new SSIS package template in SQL Server Business Intelligence or SQL Server Data tools for Visual Studio 2012 on wards which is given below: 

1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.  
2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK. 
3. Add the items that we want from the Toolbox to the Package.dtsx file. Once we are done with package template development, Click File, and then click Save Selected Items. (Important Note: Give a package name that describes the functionality of the package.)
4. Click File, and then click Save Copy of PackageName As. Here file name is the name of package. In the Save Copy of Package dialog box, click File System in the Package location box, type the following path in the Package path box, and then click OK. In this path, drive is the hard disk where Microsoft Visual Studio is installed:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\ Common7\IDE\ProjectItems\DataTransformationProject\DataTransformationItems

Note: You must have the admin credentials before done the above job. Type the path of the Visual Studio 2012 folder in the Package path box in case you didn't use the default location to install Visual Studio.

Guidelines: Use Package Template in other Project or Solution - After creating the package template and saved it on the defined location, we can reuse the same package n-number of times by using the following things. 

1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project. 
2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK.
3. In Solution Explorer, right click on Project name, click on Add and then New Item... Under Visual Studio installed templates, click the template that we want, type a name for the template, and then click Add. 

Note: The default name for the template in the Name box is the name of the template plus an incremented counter. For example, if the template name is IdealSSISTemplate.dtsx, the default name is IdealSSISTemplate1.dtsx.

Please keep in mind, if you want to do some modification in the existing package template then it should be good to have to keep the original one also. You could have parent and child package template also.

References: https://support.microsoft.com/en-us/kb/908018