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?
--------------------if it is the same database, I have to ask why DTS and why not just a INSERT INTO? How big is the table?|||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|||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.|||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|||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.|||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