One of the great selling points of ETL tools such as SSIS is that is is possible to move data from source to destination without "touching disk" as they say. By this I mean that data can be extracted from source and inserted into a destination without having to stage it anywhere first. Transformations and aggregations can be applied to the data in transit without having to rely on another engine (such as a relational database) to do these calculations for you.
That's really powerful functionality but it does present a few problems:
-
Debugging is difficult. You have to rely on the in-built debugging tools which as yet are nowhere near fully matured.
-
During debugging you have to re-open a connection to a source system in order to get the data to be debugged. This can be a major major problem if you have to access live transactional systems during the working day.
-
Back pressure from components in the pipeline can cause connections to systems of record to be held open longer than they should ordinarily have to be.
-
If it takes a long time to get data from source then this will increase the amount of time that you have a connection open to the destination (This is the opposite problem to the previous bullet point. Sort of.)
-
If you are using SSIS transactions, Distributed Transaction Coordinator (DTC) will try to enlist the source in a transaction and this may not be what you want.
Staging data can mitigate these problems. Staging most often means inserting data into a relational database table but with SSIS that need not be the case. SSIS provides a proprietary binary format file that offers super-fast loading into and unloading from the pipeline. They are called raw files.
In the SSIS work that I am currently doing on a day-to-day basis I have developed a new course of action for extracting data from systems of record. In order to avoid the problems listed above I drop the data from source directly into a raw file and then use that raw file as the source for all of my transformations and aggregations.
The big advantage that I find is when problems occur, I don't have to go back to the system of record to get the data I need to investigate. Its available in a local (thus faster) proprietary format and I don't have the DBAs of the source systems moaning at me for constantly hitting their systems.. Even if you don't want to go into production with a package that does this there is nothing wrong with holding a local copy of the data in a raw file for development purposes and then at a later date replace your raw file source adapter with the original OLE DB Source adapter. Its as simple as it looks in this screenshot (i.e. very):
I'm not saying that you should adopt the same approach but it is worth considering. My productivity has increased greatly since I started doing this.
-Jamie
UPDATE, 2008-05-19: I've just started on a project where I'm using SSIS on a 64bit machine to extract data from an Excel file. Some of you may know that in this situation you cannot run the package from BIDS because there is no 64bit Excel OLE DB provider, instead you have to use the 32bit version of dtexec.
So, if you follow the technique explained above you can dump your data from the Excel file into a raw file and use 'Execute Task' within BIDS. Happy days!