January 6, 2014 at 2:09 pm
Hi SSC,
The extent of what I've used SSIS up till now has pretty much been moving data between environments (truncate table, insert from source to dest), but I now have a need to use it much more as a true ETL tool. For the sake of argument, let's say all the sources I'm integrating are in SQL, just on different servers and databases. I may have to gather hundreds if not thousands of distinct data points (different tables, databases, servers).
My question (hopefully I'm asking the right one) is this: Would those different data flows typically be handled by hundreds of custom individual packages in one solution, all the data flows in a single package just jam-packed with data flow tasks, or some sort of foreach iterator and loop through all the possible configurations of data retrieval one at a time, just modified with descriptive meta data (such as server, columns, tables, where clauses, etc).
January 6, 2014 at 2:41 pm
Gabe T. (1/6/2014)
Hi SSC,The extent of what I've used SSIS up till now has pretty much been moving data between environments (truncate table, insert from source to dest), but I now have a need to use it much more as a true ETL tool. For the sake of argument, let's say all the sources I'm integrating are in SQL, just on different servers and databases. I may have to gather hundreds if not thousands of distinct data points (different tables, databases, servers).
My question (hopefully I'm asking the right one) is this: Would those different data flows typically be handled by hundreds of custom individual packages in one solution, all the data flows in a single package just jam-packed with data flow tasks, or some sort of foreach iterator and loop through all the possible configurations of data retrieval one at a time, just modified with descriptive meta data (such as server, columns, tables, where clauses, etc).
For very large numbers of distinct sources, I tend to use the generic metadata approach (your last option).
You could also use BIML to generate all of the different packages using the available metadata.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 10, 2014 at 11:03 am
Additionally, I tend to go with one package per target table loaded--with rare exceptions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply