Day 1 – Getting Started

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.

Test Package

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.

test_package_control_flowtest_package_task_properties

 

 

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:

SQLTask:SqlStmtSourceType=”DirectInput” SQLTask:SqlStatementSource=”USP_TEST_PROCEDURE”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s