Link to home
Create AccountLog in
Avatar of Paula DiTallo
Paula DiTalloFlag for United States of America

asked on

SSIS Approach: Seeking advice

Techies--

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? User generated image
ASKER CERTIFIED SOLUTION
Avatar of SThaya
SThaya
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Yes, it would be good candidate for SSIS .. the package can be built easily as it sounds