This series of articles relies on a basic understanding of SSIS. The approach is currently used by a large corporation with over 1000 SSIS packages scheduled by a centralized service, not SQL Agent. To date, more than 1,000,000 executions have been recorded in the history. Data is normalized in a manner that has enabled the logs for all executions to be stored on less than 15 GB of disk space. To date, none of the executions have been purged from the system allowing users to view timings of jobs executed in years prior.
For testing purposes, I have created a package named “test” stored on my desktop, not within SQL. Yes, the name should be far more descriptive for production, but for today, our focus will be on some of the internals of SSIS. The package has one connection and one task.
What’s in the box?
Microsoft completed a massive upgrade from DTS to SSIS with the 2005 release. SSIS now uses XML to store all package properties. You can open, and with great care, modify the package in a XML or text editor. I would not recommend this for a production fix, but I have used the approach in the past to perform a mass update of all table names. In the case my test package, you can find the following: