Over the past few months my blog postings have centered on some of the new functionality within SSIS. Demonstrating it and using it has been this blog's raison d'etre. I hope it has been useful to a few people!
What has become increasingly evident from feedback both here and on the beta newsgroups however is that learning the new functionality isn't the difficulty that people are facing. Understanding the new paradigm of SSIS prior to actually building anything in anger is the real challenge.
Many people are, understandably, building SSIS packages with their DTS hats on and are running into problems when they find that SSIS either a) does not do something that DTS does or b) does it in a completely different way. I had exactly the same problems when I first started looking at SSIS. Some examples of questions that I see being asked on the beta newsgroups are:
- How do I access DTSGlobalVariables collection from the script task? (Answer: You can't, it doesn't exist anymore.)
- How do I get a reference to my package in the script task? (Answer: The DTS object provides access to some objects and collections of your package. Type "DTS." in a script task and intellisense will take over from there!)
- How do I change the metadata of a data-flow at runtime to enable me to load files of different formats? (Answer: You can't!)
- Help! Where has the Dynamic Properties task gone? (Answer: It has indeed gone. Configurations are the replacement in SSIS.)
- Where have workflow scripts gone? (Answer: They have disappeared as well. Use expressions on your precedence contraints to achieve the same thing)
- How do I pass values from a child package to a parent package and vice versa? (Answer: Quite often you don't have to. The child task can reference variables in the parent package)
- How can I change the location of a file from within a script task? (Answer: You can't. Use property expressions instead.)
The one thing I learnt very early on is that you can't think of SSIS as an upgraded DTS; it is a completely new product from the bottom-up. Part of the education process in the run up to RTM is to make sure that people understand that. To that end, check out this article that I wrote for SQLServerCentral that I hope begins to bridge the divide between these 2 fundamentally different products!
Throw away your old DTS-based pre-conceptions of what an ETL tool should be and step into the new paradigm. Its bigger, better, faster and cleverer. And you're going to love it!
-Jamie
Update:
Interestingly I came across a situation just this morning (a couple of hours after writing this blog entry) where a colleague of mine was "guilty" (for want of a better word) of just this, building SSIS packages with a DTS mind-set. DTS was often termed an ELT (or as I prefer, ELTL) tool rather than ETL tool because it required the designer to stage data from a file into a database in order to be able to carry out any useful transformations on the data. SSIS alleviates that requirement by carrying out transformations on the data in memory.
My colleague was loading data from a file into a temporary staging area using an SSIS data-flow before using another data-flow on it to carry out her transformations - that's the DTS way of doing things. Quite simply this interim step is not necassary with SSIS as it allows you to carry out the required transformations when the data is staged.
N.B.
- ETL = Extract, Transform, Load
- ELT = Extract, Load, Transform
- ELTL = Extarct, Load, Transform, Load