Shunryu Suzuki, a Zen master who came America in 1959, once wrote that "in the beginner's mind there are many possibilities, but in the expert's mind there are few." This definitely seems to be to of SQL Server 2005 Integration Services (SSIS). But not always in a good way. If you are coming to Integration Services from SQL Server 2000's Data Transformation Services there are a number of such "moments of Zen" waiting for you. One of the most significant ones is that the two products are designed around two different patterns. SSIS is designed to support the Extract, Transform and Load (ETL) pattern where and DTS is geared towards Extract, Load and Transform (ELT).
The difference in these two is much more than simple word order. In simple terms, the ELT pattern relies on the power of a database store to do the work transforming extracted data from the source schema to the destination schema. The ETL pattern, conversely, normally uses a specialized engine to stream data from the source schema, transform it then load it to a destination database. Both patterns have different strengths and weakness. For example ELT maximizes the data store but normally lacks the ability to difficult transformations easily. ETL engines have more sophisticated transformation capabilities but tend to heavily tax system resources while running.
This difference in patterns tends to frustrate those who are making the transition.
But as most any Zen practitioner will tell you, Zen functions in non-duality. So to really understand the Zen of SSIS, we must understand that it functions in non-duality as well. True, SSIS is designed around the ETL pattern but that does not mean that you must -- or even should -- avoid using the ELT pattern with it sometimes. In this particular project, we have an example of that. One of the things that SSIS does not currently have a multi-value collection (e.g., a sorted key-value dictionary) that can be passed between tasks. In cases where we want that, it makes sense to leverage a database table.
A hallmark of DTS is as being a simple tool for getting simple tasks done quickly. However, its limitations make doing complex tasks fairly hard. SSIS conversely makes doing complex things easier but because of the ramp up it takes to learn the new pattern and new tools, it is fair to say that it makes doing easy thing initially harder.
I believe one the key things that the SSIS masters understand is this Zen. They know the duality of when to use what and how while understanding when not use something and why. They seek to understand the strengths and weakness of the patterns and the tools. At the same time, they seek to integrate these understandings into a highly effective suchness.