A very interesting book on real case SSIS design solutions is Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution Erik Veerman, Jessica M. Moss, Brian Knight, Jay Hackney
One of the first chapters is very interesting, the authors show how to develop a SSIS Framework capable to login the package execution at task level. In practice some tasks are executed on key events such as OnExecute, OnError ect to gather relevant info and save that error info in a support db
The second good advice is to use package configuration using environment variables, so the deployment on different machine would be hassles…
What I did is to add an extra task to clear the package logs and map a variable in the configuration to enable disable the task (through an expression)
You can find the solution with the SSIS packages here
and you can copy the template package in
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
in this way when you add a new item in a SSIS project you can choose the new template with the template.
The extra code (compared to the code in the book) is about the logs table handling
Changing the value of the CleraTabLogs entry you can decide to reset or not the log tables
You can check that in the sql profiler if you like
I advice to read the book to understand the flow first.
CRS is a project I am working and for which I created this code…sorry no time to celan up the names….