October 8, 2007 at 7:22 pm
I'm able to connect to the Oracle database to insert the data into multiple tables using OLEDB connection via Oracle Provider for OLEDB. However, i wish to create a transaction so that i'm able to rollback all the data in the case where the insertion fails in one of the table. May i know how can we create a transaction in SSIS for Oracle destination?
Thanks.
October 11, 2007 at 6:51 pm
Sounds like you might have to resort to a script task, using ADO.NET and the Ole db provider for Oracle. You would basically define a transaction, apply it to each command object doing the insert - then executenonquery in a try - catch block.
October 16, 2007 at 7:39 am
Mike Bishop (10/11/2007)
Sounds like you might have to resort to a script task, using ADO.NET and the Ole db provider for Oracle. You would basically define a transaction, apply it to each command object doing the insert - then executenonquery in a try - catch block.
Hi Mike,
Thanks for the info. Currently, i have 11 data sources that is needed to update into 11 target destinations. These 11 data sources have no link with one another, therefore i will need 11 Data Flow Tasks to pump data into the target destination. I'd tried to use Script Component, to do the commit and rollback, however it only applies to the particular Data Flow Task. Any idea how should i go to create a Transaction rollback/commit for these 11 set of data sources?
Thanks!
October 16, 2007 at 11:56 am
Hi loi
Yes. I was suggesting that you replace the 11 data flow tasks with one script task and that script task would have the code in it to do programmatic inserts. Is this correct?? I'm assuming all you are doing is an insert!?? Your code would have two ArrayLists - one for the source connection strings and one for the destination connection strings. So you write a loop (outside loop) that opens the source and destination and moves rows from one to the other, and finally closes the source and destination. Inside the loop you write another loop that process all the rows from the source via a data reader.
Tons of code examples in msdn. Good luck and have fun out there.
-Mike
October 21, 2007 at 6:43 am
Hi,
You can set TransactionOption property of Data Flow task or package and use transaction (you can rolled back in case of failuare)
Omkar.
October 22, 2007 at 5:48 am
Hi loi,
I think 'lomkar' is on an interesting track. Use SSIS to setup one 'parent flow', with TransactionOption='Required', and
10 child flows with TransactionOption='Supported'. Combine this with the technique of 'containers', so that the 10 children
are within the parent container, a single transaction will last from the start of parent until end of parent. And if any
step/container fails the whole transaction fails and a rollup will take place for all 11 inserts.
NOTE!!
SSIS transactions require Microsoft Distributed Transaction Controller service to be running on the same machine
that is running SSIS
==> See article by Jamie Thompson from 2005/01/03 for more details:
Regards,
BF
October 22, 2007 at 5:57 am
Bertil Forsberg (10/22/2007)
Hi loi,I think 'lomkar' is on an interesting track. Use SSIS to setup one 'parent flow', with TransactionOption='Required', and
10 child flows with TransactionOption='Supported'. Combine this with the technique of 'containers', so that the 10 children
are within the parent container, a single transaction will last from the start of parent until end of parent. And if any
step/container fails the whole transaction fails and a rollup will take place for all 11 inserts.
NOTE!!
SSIS transactions require Microsoft Distributed Transaction Controller service to be running on the same machine
that is running SSIS
==> See article by Jamie Thompson from 2005/01/03 for more details:
Regards,
BF
Hi BF,
I'd tried setting the parent flow TransactionOption to Required and the child flow TransactionOption to Supported. I'd also have my MDTS running and Oracle Services for MTS installed. However, when i tried to run the package, it prompts me for "Unable to enlist the transaction". I'd tried the same mechanism for SQL Server database and it's working. Any idea?
October 23, 2007 at 5:41 am
loi,
seems like this is a wellknown problem and the solution should be to install the latest service
pack for SQL Server 2005. Microsoft says that, if you need the info, you can read more in their Knowledge Base article 913089("How to obtain the latest service pack for SQL Server 2005").
Hopefully this service pack will solve your problem.
/Bertil
December 20, 2008 at 1:05 am
Linux and Oracle doesn't support SSIS transactionOption.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply