This is a bonus Tech Talk Thursday by J’son on a Tuesday! We are a wild bunch!! Enjoy!
Usually, I design my SSIS packages into two sections. A Staging Group that is responsible for bringing in source data and a Final Group that is responsible for normalization, data mart design, et. In the Staging Group, I either bring my source data in all at once or do an incremental pull – it really depends on the amount and type of data needed.
In both cases, I need to make sure that I have a table in my Staging database that is ready for the data I’m about to pull in. Let’s take a typical Staging process:
You can see that each individual import is made up of two parts – the Execute SQL Task is responsible for either drop and recreating or simply building once (if not exists) the SQL table needed for the data. Then the Data Flow task imports the data and fills the table.
Lately, some of the SQL scripts I have been using to get source data have been getting seriously long with lots of columns needed, etc:
To get the script and the rest of this post, download the complete Handy SSIS Trick – Use Temp Table for Schema Generation for Execute SQL Task Teck Talk.