Results 1 to 7 of 7

Thread: SQL vs DTS

  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: SQL vs DTS

    i've got some SQL scripts which cleans and truncates the data, i run them using a batch script which i put it in a SQL job? i was thinking more of DTS packages since the it is easier to troubleshoot and the SQL script has gone over like 12 pages. Would it be better to change ?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jcwc888
    i've got some SQL scripts which cleans and truncates the data, i run them using a batch script which i put it in a SQL job? i was thinking more of DTS packages since the it is easier to troubleshoot and the SQL script has gone over like 12 pages. Would it be better to change ?

    A DTS packag easier to troubleshoot than T-SQL.....wow

    Converting a 12 page T-SQL Script into DTS......wow

    1. Why not create the script as a stored procedure

    2. You do know that M$ didn't like DTS enough in it's current form to keep it around for 2005....so why travel down a dead end....

    Wow....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144
    i guess the answer is i'm keeping to the t-sql script.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But what's with the twelve pages.

    PLease provide a sample of what you're doing.

    Does it involve cursors?

    Look at the sticky above and post some sample code.

    Also, this is only my opinion, so take it as such.

    I'd like to see a code snipet...or better yet, just attach a text file with the 12 page sql
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A good SQL script for importing data can easily extend over 12 pages. I use DTS only to load data into staging tables, and then I have rather lengthy (and thorough) TSQL procedures for cleansing, reformatting, and loading the data. The scripts loop through the data in batches, processing oldest inserted records first so that the system doesn't get confabulated if the inputs and processing are not run on regular schedules. For each loop I verify the data and translate any codes necessary, storing the results in table variables. Then I run two or three passes against the temporary data set to insert, update, and (when required) delete the data in the production tables. I also check to make sure the data has changed before applying updates. Finally, I log any records that could not be processed, and then continue the loop to the next batch until the staging table is empty.
    Yeah, the code is long, but the process doesn't bust and it handles bad input gracefully.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    The scripts loop through the data in batches
    Huh?

    processing oldest inserted records first so that the system doesn't get confabulated
    That's a technical term

    if the inputs and processing are not run on regular schedules. For each loop I verify the data and translate any codes necessary, storing the results in table variables. Then I run two or three passes against the temporary data set to insert, update, and (when required) delete the data in the production tables. I also check to make sure the data has changed before applying updates. Finally, I log any records that could not be processed, and then continue the loop to the next batch until the staging table is empty.
    Yeah, the code is long, but the process doesn't bust and it handles bad input gracefully.

    Dude, you're fired.

    If you're doing this, you have to play through all the transactions in the order they came in. How can you possible make business decisions on the transaction for every permutation.

    You need to replay everything....

    Got a sample to "open my eyes"?

    Been over to tek-tips...man those people are painful

    uuhhhh...how do I turn on my 'puter?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, not every transaction.

    Each loop through the staging table processes the oldest instance of each natural key. So if say, the import process was run twice during the day and the dataload process is run nightly, only two loops through the data would be necessary.

    Please don't fire me. I have four kids to support. I'm begging you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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