Hi,

in SSIS I've created a package that transforms data using several steps. I want to log each step in my [Jobs] table, so I've added Execute SQL tasks between each step to update the [progress] column in that table.

Here's the problem:

The transformation runs in a transaction, however, I don't want to have the progress update statements run within this transaction. Otherwise no-one could read any progress update until the whole package is committed.

I've tried to assign a second connection to the progress tasks and to set Transaction to NotSupported, IsolationLevel to ReadUncommitted. But then, the progress tasks just hang. What am I doing wrong?

Can anyone please enlighten me on how to achieve this?

Axel Dahmen

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hello Axel:

If all the operations are going against the same server, set its Connection
Manager Propert "ReatinSameConnection" to True

Now, on the Control Flow, create an Execute SQL Task connected to it with
the statement BEGIN TRANS

Down stream of that, do Data Flow 1, then another Execute SQL Task to Log
Step 1. Next is another Execute SQL Task with COMMIT TRANS.

By the way, you do not need any ROLLBACK TRANS steps because if anything
fails, the ROLLBACK happens automatically.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Axel Dahmen" wrote:

Hi Todd,

thanks for trying to help!

But I guess that's not quite what I'm searching for... I don't want to run logging and worker tasks within the *same* transaction, I want to run the logging tasks *outside* of any transaction. Otherwise no-one outside of SSIS could actually see any progress while some long-taking tasks are running. See the difference here?

Would you know a solution for this scenario? Or does setting "RetainSameConnection" to true also prevent those logging tasks from blocking the others if Transaction is set to NotSupported for them?

Your help is quite appreciated.

Best regards,
Axel Dahmen



--------------
"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag news:431D40FE-8C35-4B63-89A0-66579498B737 (AT) microsoft (DOT) com...