Hi Janet, thank you for writing your experience using DTS to load the data into your stage tables and into your dimension tables. I had similar experience, using DTS to load the data into stage then into fact and dimension tables from different source systems: spreadsheet, AS/400, DB/2, MS Access and flat files. I did similar approach, i.e. using dynamic process, for the same reason (migration between Dev-QA-Prod environments) but instead of reading from SQL table, the dynamic package reads from a parameter/control file. I had the staging database name, names and locations of files to be imported etc in this parameter file. The reason of using a file is that we had different SQL Servers for Dev, QA and Prod env so if the dynamic process reads from a SQL table in SQL Dev for example, when the DTS packages are migrated to Prod env it can't access SQL Dev firewall constraint).

We had about 40 or so sources (tables or files) for fact & dimensions from about 5 or 6 different source systems. Instead of creating a DTS package for each of this 40 sources, I created 1 DTS package for each data source. For example: 2 packages for DB/2 (they have 2 different ERP systems runing on DB2), 1 package for the XLSes, 1 package for the MS Access DBs etc.

I also share the same situation as you about SOX, i.e. there is a lot of procedures and constraints in the production because of Sarbane Oxley, and because of best practices too

Thanks again for sharing your experience.

Kind regards,

Vincent