MatthewRoche
(Sorry for the delay in responding - the real world has been intruding today 
)
Thanks for all of the additional information!
Based on what I know, this is the basic approach that I would look at for your SSIS package:
- Add these string variables to your package:
- LogFileName
- SourceTableName
- SqlQuery
- Add a Flat File connection manager to your package. Add an expression to its ConnectionString property so that the property value is set by the LogFileName variable
- Add an OLE DB (or whatever is appropriate) connection manager to your package, and configure it to reference your source database.
- Edit the properties of the SqlQuery variable so that it is evaluated as an expression and its expression yields a SQL SELECT statement that selects records from the log table identified by the SourceTableName variable. Something like this might work as a starting point for an expression - "SELECT * FROM " + @[User:
ourceTableName]
- Edit the properties of the LogFileName variable so that it is evaluated as an expression and its expression yields a file path to the log file to use for that table.
- Add a Foreach Loop container to your control flow, looping through the source tables feeding into the log table and storing the table name in the SourceTableName variable as the loop enumerator.
- With this setup, for each source table being enumerated, the SqlQuery variable will contain a SQL SELECT statement to query from the "current" source table, and the LogFileName variable will contain a file path for a log file into which records from the current table should be dumped. This is probably the most important conceptual leap moving from DTS.
- Within the Foreach Loop container, add a Data Flow task.
- Inside the Data Flow task, add the following components:
- An OLE DB (or whatever is appropriate) data source that uses the connection manager from step 3, and uses as its source query the SqlQuery variable from step 1.3
- Whatever transformations (derived columns, conditional splits, etc.) are necessary to implement your core logic.
- A Flat File destination that uses the conneciton manager from step 2 (remember - the flat file connection manager uses an expression with the LogFileName variable as its connection string, so it will "automatically" point to the correct flat file path if you've set it up correctly.)
- Within the Foreach Loop container, add an Execute SQL task that updates the necessary records to show that they have been processed.
I realize that I've not addressed every one of your requirements, but hopefully this will give you a good starting point to solve your problem.
Also, since you are new to SSIS and since this is not a trivial exercise, it would be a great idea for you to create a few sample/starter packages that use the different techniques described above in a simpler package. If you try to bite off this whole thing all at once, it's probably going to be an exercise in frustration.
Enjoy!
P.S. Please reply only to these forums, not via email, so that the entire community can benefit from the issues raised on the forums.