June 14, 2011 at 2:11 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:
Script task 1: Read audit table get last start time and create this start time.
Data flow 1: Read from Informix where date between last start time and this start time.
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 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:
June 14, 2011 at 2:28 am
Duplicate post. No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1124791-364-1.aspx
Jim, I'd suggest you attach the screenshot to the other thread as that is the one with more detail
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply