Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007

    Question Unanswered: DTS packages and transaction

    Hello everyone,

    My question:
    Is it possible to gate the complete execution of a DTS package?

    -I have to databases, dbSource and dbArchive.
    -Each database has 1 table of the same format, tableData

    1- I put 50 000 rows in dbSource
    2- Once the insertion completed, I start my DTS package. The package does this:
    2.1 - Move all rows of dbSource in dbArchive
    2.2 - Truncate rows from dbSource
    3- Immediatly after the DTS had start, I start the insertion of another 1000 rows inside dbSource.

    Once everything is terminated (DTS and 1000-rows insertion), here is the count inside each database:


    So I "lost" 40 rows in the process.

    My expectations:
    By setting the package property Use Transactions with the Serialization isolation level, I thought that the reading part of the bulk move would be gated, so any insertion in dbSource would be officially inside dbSource after the DTS completion only.

    Feel free to ask any other information you might need. I can also explain my problem from a higher point a view and what I try to achive.

    Thanks !

  2. #2
    Join Date
    Dec 2002
    I think that this is one of the major enhancements that is available to you in SQL 2005 (SQL Server Integration Services).

    Be prepared for a steep learning curve.


    Have you hugged your backup today?

  3. #3
    Join Date
    May 2007
    Thanks for the info.

    Unfortunately, I cannot move to 2005 on this project I run...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts