SSIS issue when run with transaction

  • Hello All,

    I am able to run SSIS package as an scheduled job when the transaction option is set to supported everywhere. When I set Transaction option to 'required' on top level container it continues to run forever (usual time to run is 35-40 min). Eventually I have to cancel the job. Max time I have let it run is about 3 hours. Not sure what the issue is.

    Also wondering if there is any other way to make SSIS package as transactional using different job steps.

    Any help will be greatly appreciated

    Thanks

  • Anyone here knows what it could be?

  • Ahhh, the dreaded silence after posting which leaves you with that sinking feeling that maybe you are the only one experiencing this particular errant behaviour 🙂

    It would be useful background if you would explain a bit about what the package does and exactly what you are trying to control within a transaction.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I also had a similar problem. But after a while the packages started erroring out. Turns out you need the MSDTC service on the same machine where the packages are running.

    http://blogs.conchango.com/jamiethomson/archive/2004/12/14/SSIS_3A00_-Package-transactions.aspx

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8ee5ba3c-6195-4c7f-b0c5-cf6d68da4629/

  • Well, it is pretty simple package created through wizard. I am importing the data from another database at different location to our server. It simply copies the data into each table with same schema with the option to delete all rows instead of append. I do not check the optimize for many tables since it tries to create temp files on my machine since I am the one who is creating it, but since the package is run from the server it tries to locate those temp files on the server and it fails to run.

    I save it as a file and open it using business intelligence and change the "Transaction Option" to required and save a copy to server . When I run it it just says executing on Job Activity Monitor and runs forever. But if I leave the transaction option to supported it runs fine for about 35 mins.

    I hope this detail would help to figure out. let me know if any specific questions.

    Thanks

  • Thanks for this bit of info as I wasn't able to locate this issue after numerous searches. I have let the package run for about 3-4 hours and then cancelled as it is totally unacceptable time compared to 35-40 mins. Apparently it doesn't give any error message in the job history, but says "Job execution was cancelled".

  • I have had so many issues with TransactionOption in SSIS that I don't use it anylonger. It is not a feature within SSIS that I trust.

    A shot in the dark. At 40mins sounds like you are importing a lot of data. With Trans Option set to On is this Logging the details in the trans log in order to roll back ? Is the db recovery model set to Full. Try setting to simple. Also look at the options in the destination transform task for committing any data in batches.

    Paul R Williams.

  • When you run in a transaction, could you be getting blocking on the source server?

    SQL = Scarcely Qualifies as a Language

  • Thanks for you suggestions... Since it doesn't show any error I cant say if there is any blocking or not... but last time I let it ran whole night for upto 8 hours and it was still running.

    I do have lots of data. Actually I copy all the tables in the database thats why it takes long. So, instead of going through options and settings, how about if I go through the following steps in scheduled job.

    - Create temp database with same schema

    - Copy all data in the temp db using the existing package (Not sure how to tell package to copy in temp db dynamically)

    - drop the original database

    - Rename temp db to have its original name

    I know it wouldn't be equal to transactional feature but atleast I will have old data if package fails to execute properly for any reason

Viewing 9 posts - 1 through 8 (of 8 total)

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