SSIS Package Template

The project I am working currently needed a template all ETL developers can use as a standard package. This will be the beginning point of each package. I have written a simple package to act as a template.

I wrote the package and called it as “Pkg_Template.dtsx”. Template package(s) need to locate in certain location for it to work. You would need to put this SSIS package in the following location on your machine

%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

Once you have placed the template package in the above location you can open the template package by Right click on a project and select “Add” and “New Item”. This will bring a screen like the one in the link below :
http://www.smart-bi-solutions.com/BlogStuff/06012005_Pic1.jpg

As you can see from the above screen shot the Pkg_Template does appear within BIDS template section. You can give new name at this point or it will give a default name of “Pkg_Template1.dtsx”.

The screen shot in the link below explains what template package does:
http://www.smart-bi-solutions.com/BlogStuff/06012005_Pic2.jpg

The above template does the following:

1. Retrieves LoadId from SQL Server using Stored Procedure.
2. Checks for LoadId Validity.
3. Configures SSIS auditing using Execute SQL Task (Expressions),
4. Uses “Environments.dtsconfig” to set the SQL Server connections.
5. Package is configured to log to Windows Event Viewer if the package errors out.
6. Event Handler to do auditing, for example, it logs what time task started and completed etc.
7. I have put some comments for the new developers in the future to see what the package does etc.

I hope this is useful.

Thanks
Sutha