I'm looking at a requirement that is a good candidate for an SSIS package. Yesterday I created a t-sql script to extract the data as it would look in its final state for the export. To extract/transform that data I did the following things in this order:
1. Create a temp table (e.g. #tempTableName)
2. Inserted as much as possible to the temp table with an INSERT SELECT statement.
3. Updated the temp table with correlated queries (includes cleanups, flattened columns and transforms)
4. Selected the distilled data from the temp table
Interactively, I copied the data with headers and dumped it into an EXCEL spreadsheet for review.
The missing 5th step for SSIS would be that the extract would need to dump a CSV copy of the data to an FTP location.
The missing 6th step(s) for SSIS would be error handling.
What is the best way to approach this in SSIS? For example, should I still act against a temp or staging table?
Your advice will be greatly appreciated.
BTW, I noticed the container in the pic frequently used to logically group tasks. I don't see it in the toolbox. How is it created?