How to restrict to stop the package if one of the excel tab gives error

Question

How to restrict to stop the package if one of the excel tab gives error

sqlstud

SSCrazy

Points: 2527
More actions
December 20, 2012 at 4:12 am

#271841

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

sqlstud SSCrazy Points: 2527 More actions · Answer 2

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

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 3

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

http://technet.microsoft.com/en-us/library/cc304421.aspx

sqlstud SSCrazy Points: 2527 More actions · Answer 4

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
http://technet.microsoft.com/en-us/library/cc304421.aspx

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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