December 20, 2012 at 4:12 am
Hi,
I have developed a SSIS package.
Source : XLSX File
Destination : SQL Server Table
The XLSX files contains 4 tabs. I am loading 4 tabs into 4 different tables by creating 4 data flows.
Steps:
inside For each loop container
1. Used Script Component
i have checked whether the given file is valid or not. if valid, it executes the step 2. if not valid, it send out an email
2. Used 4 Dataflows one for each tab. All are independent data flows
please find the strucure attached:
Ques:
If any one of the tabs get failed, it should not load the other three tabs.
Please advise how to proceed.
Regards
SqlStud
December 20, 2012 at 5:13 am
Enable transactions in your SSIS package. You can either use the built-in transaction mechanism which uses MSDTC, or you can create your own transaction by putting a SQL Task with "BEGIN TRANSACTION" at the start of your package and a SQL Task with "COMMIT" at the end of the package. Make sure you set RetainSameConnection to true for the connection manager.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 20, 2012 at 5:20 am
Koen Verbeeck (12/20/2012)
Enable transactions in your SSIS package. You can either use the built-in transaction mechanism which uses MSDTC, or you can create your own transaction by putting a SQL Task with "BEGIN TRANSACTION" at the start of your package and a SQL Task with "COMMIT" at the end of the package. Make sure you set RetainSameConnection to true for the connection manager.
Thanks MCSA.
Can you please let me know how to do it?
Regards
SqlStud
December 20, 2012 at 5:30 am
sqlstud (12/20/2012)
Koen Verbeeck (12/20/2012)
Enable transactions in your SSIS package. You can either use the built-in transaction mechanism which uses MSDTC, or you can create your own transaction by putting a SQL Task with "BEGIN TRANSACTION" at the start of your package and a SQL Task with "COMMIT" at the end of the package. Make sure you set RetainSameConnection to true for the connection manager.Thanks MCSA.
Can you please let me know how to do it?
Regards
SqlStud
One way of mainitaining explicit transaction Koen has already mentioned by putting Exec SQL Task.
For the first one please refer below URL
December 20, 2012 at 8:34 am
rhythm.varshney (12/20/2012)
sqlstud (12/20/2012)
Koen Verbeeck (12/20/2012)
Enable transactions in your SSIS package. You can either use the built-in transaction mechanism which uses MSDTC, or you can create your own transaction by putting a SQL Task with "BEGIN TRANSACTION" at the start of your package and a SQL Task with "COMMIT" at the end of the package. Make sure you set RetainSameConnection to true for the connection manager.Thanks MCSA.
Can you please let me know how to do it?
Regards
SqlStud
One way of mainitaining explicit transaction Koen has already mentioned by putting Exec SQL Task.
For the first one please refer below URL
Thanks....
But it didnt solved the problem.
it given the error as "
[Connection manager "src"] Error: The connection does not support enlisting in distributed transaction. "
Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Src" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Regards
SqlStud
December 20, 2012 at 11:07 pm
My guess is MSDTC isn't running.
You can always try with the Execute SQL Tasks as I mentioned earlier.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply