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).
Executive Junior Cowboy Developer, Esq.