Post reply

SSIS - Transactions

  • January 26, 2010 at 6:15 am

    #136668

    Hi,

    I am fairly new to SSIS and am currently struggling to make a package work as a Transaction.

    I have 3 text files I need to import and these are each imported to a Tmp1 file and then transferred to a Tmp2 file for manipulation after which they can be inserted into a Production table. At present my processs is split into 3 similar steps (each of 3 steps).

    Firstly the Tmp1 and Tmp2 files for the first import are truncated (SQL Task) then I run a Flow Task to import the data into Tmp1. Then I run a second SQL Task to import to Tmp2 and manipulate the data. The steps are then repeated for import 2 and import 3. Each Import process has its own Tmp tables. I have use 2 Tmp tables to get around issues with DataTypes. At present I do not have code to import to the production table, but this should be straightforward.

    The process runs fine until I make the TransactionOption "Required" at which point it seems to hang as soon as the first Flow Task is accessed. So I am thinking that it is something to do with Locks on the Tmp tables, caused by the initial Truncate command (which succeeds). I have tried including the processes in a Sequence Container, but still get the same issues.

  • January 26, 2010 at 6:17 am

    #1108599

    Not sure what happened there, but just wanted to say thanks for any suggestions that people may have.

    Cheers

    Colin

  • January 28, 2010 at 11:09 am

    #1110249

    Colin Betteley (1/26/2010)

    Not sure what happened there, but just wanted to say thanks for any suggestions that people may have.

    Cheers

    Colin

    What's happen with this issue? Did you make it work?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 29, 2010 at 12:45 am

    #1110506

    Hi,

  • January 29, 2010 at 12:47 am

    #1110507

    The issue is still outstanding.

    Posts seem to go before I finish writing them! Sorry about that.

    Any help in this area would be gratefully received.

    Thanks

    Colin

  • January 29, 2010 at 1:36 am

    #1110535

    This should make it work but may not be matching your requirements (in either case let me know):

    I assume you use an execute SQL task for the truncate. Change the TransactionOption property for this one to NotSupported.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • January 29, 2010 at 1:53 am

    #1110544

    Here is a screenshot. You can see that the first SQL (Truncate) works, but it then hangs on the first data Flow task.

    Colin

  • January 29, 2010 at 2:05 am

    #1110555

    Dou you really need to implement this as one transaction?

    This is how I would do it (without knowing your exact requirements :-):

    create 3 sequence containers and put the process for each file (PD, PS and ST) in a separate container.

    transactionoption on the package level = supported

    transactionoption on containers = required (this will create separate transactions for each process)

    transactionoption on truncate = not supported

    transactionoption on data flow task = supported

    Works as expected on my machine.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • January 29, 2010 at 2:28 am

    #1110568

    Thanks. Issue could be that truncate command does not go through the log file and therefore cannot be part of a transaction.

    There are 3 more steps to include until I get to the final requirement, but will take on board your suggestion and redsign/rewrite the package. This may not be for a couple of days, but I will post back the outcome of the changes.

    Many thanks for your advice.

    Colin

  • January 29, 2010 at 2:46 am

    #1110578

    You're welcome.

    I also found this. Tried it on my machine and it works.

    You don't have to change ValidateExternalMetadata to False on ALL source and destination components as the article recommends - just changing it on the components that use the table you are truncating is enough.

    Seems like a bug in the Data Flow Task if you ask me. I tried replacing the Data Flow Task with an Execute SQL Task (that does exactly the same) and then there is no problem.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • January 29, 2010 at 3:21 am

    #1110597

    I have moved the truncate command to outside the Sequence Box and it now seems to be working as required.

    Great to chat and thanks for your time.

    Colin

Viewing 11 posts - 1 through 10 (of 10 total)

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