Hi,

I am doing this using stored procedures interacting with SSIS 2008 R2. My testing is to run against the entire data set.

The code I am generating is a select statement executed in a MySQL database which creates an export file for loading via a SSIS file import task. The original five different MySQL stored procedures gave five different files and had five separate, but very similar, SSIS packages to load them into the same data destination. (A Data Warehouse.)

By writing a stored procedure to write the required MySQL select statement I was able to have only one SSIS package to load the data. This could then be encapsulated by a "Looper" package, which would call the new SSIS package. It uses expressions extensively to shape sources and destinations for the package.

Outside of ensuring that the package fails safely, and can be run against the entire initial data set, the code generation is based on fixed tables, not the data itself, so there is a finite number of possibilities.

There are still cases I have not expected and I've learned a great deal about the SSIS task inconsistencies. (The Sort data flow task defaults to case sensitive, but the partial option of the Lookup Transformation tast seems to be case in-sensitive.)

I'm sure that I don't always use SSIS correctly, I tend to used stored procedures to transform large data sets, but I'm reading and learning to do better.

Thanks,

Peter