I've said on numerous occasions, in slightly different ways, that making the move from DTS to SSIS is not a simply a matter of learning a new technology. Its a new way of thinking as well.
I have come across many examples of people coming unstuck in SSIS because they are trying to accomplish something in the same way that they used to in DTS and finding that it just isn't possible. That's perfectly understandable of course, and I went through the same pain points myself when I was new to SSIS.
For these reasons I thought it would be useful to compile a list of DTS "do"s that have become SSIS "don't"s. More usefully perhaps, I've tried to explain how one would go about accomplishing the same thing in SSIS.
In order to make it easier to digest some of this stuff I've seperated these out into a series of posts that you will see coming out over the next few days. I've started with one of the most common questions - "How do I modify a package when it is executing using a script task?"
Modifing a Package in script
Probably the most common example exhibited of people not being able to make the jump from DTS to SSIS is the common complaint that a SSIS package cannot be modified at execution time from within a Script Task. Modifying a DTS package in this way using the DTS object model was a popular technique amongst seasoned DTS developers so it is not unreasonable for them to expect to be able to do the same thing in SSIS. Unfortunately, you can't!!
As stated above, packages could be modified dynamically in DTS at runtime through the use of ActiveX Scripts that edit the DTS object model. An executing SSIS package however cannot be modified at runtime using script. Period.
That does not mean that a package cannot dynamically alter itself based on certain conditions. The behaviour (or state) of all objects in SSIS (i.e. tasks, containers, components, variables, connection managers) is determined by how you set them up. In other words, the state of an object is determined by the values of all the properties of each object and changing those properties can change the state of the object.
Properties can be changed at execution time by using property expressions and I have previously expanded on this here: https://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx. Property expressions are the way that we dynamically change the behaviour of a SSIS package at runtime.
Having said all that, it IS possible to build a package from scratch at runtime using the SSIS API. [Note the subtle difference here from building a self-modifying package]. Kirk Haselden expands on this concept here:
Self Modifying Packages in SSIS?
http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx
Here are some links to other posts in this series
(2) Changing connections using the Dynamic Properties Task
(3) Passing a parameter from a parent package to a child package
(4) Implicitly Converting Between Data Types
(5) Changing values in the pipeline
(6) Capture number of rows with RowsComplete
(7) Capture a value from inside the data pump into a variable
(8) Get a reference, within a script, to the package using DTSGlobalVariables.Parent
(9) Add a timestamp to the data in the data pump
(10) Use multiple connection objects in order to maintain seperate connections to databases
(11) Skipping insertion of a row