Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Unanswered: DTS Transaction Trouble

    Hi Guys,

    I have a DTS Routine which archives a large amount of data into archive tables in the same database(Personally I would rather archive into a separate db but the paperwork for the customer involved in doing this is ridiculous).

    The routine seems to archive all tables except for one (the largest one) However if I split the routine and run the rest of it first then the problem table then the table archives ok. To me it smells of transaction size or something but does anyone have any other ideas.. If it is transaction size, is there a way that I can break the routine down into multiple transactions?

    Any help is as always greatly appreciated.

    Cheers,

    Chris
    ----------------------------------------------------------------
    If we were meant to count in Hex, why do we only have X fingers?
    ----------------------------------------------------------------
    Last edited by cpjames; 04-10-06 at 08:39.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if it is the same database, I have to ask why DTS and why not just a INSERT INTO? How big is the table?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2006
    Posts
    4

    DTS Transaction Trouble

    My reason for using DTS is that I needed to do some transformation of the data along the way which I did using vbscript, I had originally intended to implement the routine as an SQL script.

    Chris

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you have an error message? have you checked the log? there is not enough in your post. I am having a hard time grasping what could have been so hard as to nmot have done it in SQL. Read Brett's sticky up top.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2006
    Posts
    4

    DTS Transaction Trouble

    There is no error message, the routine simply hangs when it gets to the transform for one of the tables.

    If i disable this step then the routine runs through without problem.

    I originally created the routine using DTS because whilst currently the archive tables are in the same schema this won't be the case once the routine goes live. The live routine will most likely be archiving to a mirror database on a different server. Also I will want to run the routine as a job.

    I will post the log results here however does anyone have an answer to my original question asking if there was a way to break a DTS routine into multiple transactions?

    Thanks,

    Chris

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you have a solution in mind without having truly diagnosed the problem. DTS is a toy I avoid because it is full of issues. Even when it goes to another server you could still write it in sql and run it as a job over linked servers.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2006
    Posts
    4

    DTS Transaction Trouble

    You are putting the cart before the horse a little here.

    Apart from this issue I have already built the DTS routine so I am not in a position to easily start again so I am looking for the solution to the problem rather than settling on a solution of starting again.

    I understand your thoughts on DTS having developed solutions using the tool and other solutions using pure SQL scripts. However I have a number of customers that run similar routines on a nightly basis without issue.

    So before I resort to starting again I want to investigate resolving the immediate problem.

    Regards,

    Chris

Posting Permissions

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