mysql - Using SSIS Recordset to execute stored procedures per row

Question

I'm tasked with converting a very old & simple Java program used for daily data processing of a MySQL database to an SSIS package. The program queries the DB to get a list of records and then executes a set of 10 or so procedures with the primary key of the record passed in as a parameter. Once execution completes the next record is loaded and the process repeats. In SSIS I use an ODBC connection to MySQL, run the query and pass the results into a recordset destination. Back on the control flow tab I use a ForEach loop to load each recordset & execute the set of procedures before loading the next record. The initial loading of the data into the recordset is very fast but there is a large delay in-between each record loaded by the ForEach loop before the procedures are executed. The java program can execute in 15-20 min while the ssis package takes over a day. As a workaround i'm using SQL Server to build a cursor and a linked server to access mySQL & is similar to the Java processing time.

I know SSIS works better when designed to work in batches but were not trying to redesign the entire thing but rather move it to a language that we can better support with our current team. Are there settings i can disable to eliminate the delay between records? is this something that SSIS will never be very efficient at?

Answer 1

等待大神答复