Recordset manipulation in SSIS

Question

In my SSIS job, I have a need to accumulate a set of rows and commit them all transitionally when processing has completed successfully. If this was pure SQL, I would use a temp table inside a transaction. In SSIS there are a number of issues complicating this. It's difficult to have multiple components share the same transaction and having temp tables that do not exist at design time is a pain.

If I use Recordsets inside SSIS for this purpose, there are other issues. My understanding is that an 'Execute SQL' component will re-initialize the Recordset when it runs, so I can't use that to append an additional row. Is there a way to create an OLE DB connection that references an in-memory Recordset?

Is there a better way to achieve this result?

Answer 1

There is a better way. I'd suggest using a temp table, as you said you'd tried/considered. The keys to using a temp table with SSIS are: 1. You need to set the "retain same connection" property to true on the Connection Manager, otherwise the "smart" connection management and pooling will end up dropping your connection (and restoring it without the temp table) midway through your package. 2. You need to design your package with the temp table created already - even if you have to create it as a regular table in order to design against. When you're ready to deploy, change the table name(s) in the components that touch it, and set those tasks DelayValidation property to False. (That will prevent the package from failing because the table doesn't exist when the package is initially started.)