June 14, 2011 at 2:16 am
HI All,
I've currently got an SSIS 2008 R2 package that goes off to an Informix database, reads some data, does some transformation and puts it into a SQL Server 2008 R2 data warehouse. I'm then doing an insert into an audit table with the number of rows and times for update start and finish, this is handled in a script task:
Control flow
{
Script task 1: Read audit table get last start time and create this start time.
Data flow
{
Data flow 1: Read from Informix where date between last start time and this start time.
<transformation>
Data flow 1: Insert into SQL Server table
}
Script task 2: Insert into audit table
}
The issue I'm having is with transactions, in that if either the initial insert into my SQL Server destination table fails, or the insert into the audit table fails, the whole process should roll back. The problem lies with SSIS attempting to create a transaction on the Informix database which due to MS DTC being disabled on the Informix machine (because it runs on Linux) is failing the package from the start.
Is there any way I can avoid creating a transaction on the data source read in my Data Flow task? Ideally the data flow would look like this:
Control flow
{
Script task 1: Read audit table get last start time and create this start time.
Data flow
{
Data flow 1: Read from Informix where date between last start time and this start time.
BEGIN TRAN
Data flow 1: Insert into SQL Server table
}
Script task 2: Insert into audit table
COMMIT/ROLLBACK TRAN
}
Any suggestions much appreciated. I've attached a screenshot of the flow that currently lives inside a sequence container.
Also open to redesigning the whole package as this is still in Development.
Cheers!!
Jim.
June 14, 2011 at 2:30 am
Jim
You could get your data from Informix into a SQL Server staging table, and then copy from that into your main table. Then you would only need to wrap SQL Server operations in the transaction.
John
June 14, 2011 at 3:44 am
June 14, 2011 at 3:51 am
Jim
You can use temp tables, but beware of issues with their scope and life expectancy. I think I would just create a staging table in the database. You can clear it down when the package no longer needs the data in it.
John
June 14, 2011 at 4:14 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply