SQL Server Performance

Truncate Data using DTS(If source data exist)

Discussion in 'SQL Server DTS-Related Questions' started by kempo, Jan 14, 2004.

  1. kempo New Member

    Hi,

    I have a DTS that Delete's/Truncates all the data in my tables before transformaing the data again. 95% of the time it works fine. The problem I have is when there is a problem with the source data(ie. it does not exist) then all me data is lost because the Delete/Truncate takes place anyway.

    How would you recommend I check if source data exists and then only commit the job?

    Thanks
  2. satya Moderator

    You can control the code using SP to validate the source data existence before deleting the data.

    BTW how are approaching currently to truncate the data?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. kempo New Member

    Is there a built in SP? If not can you help me with the script.

    I am currently truncating the data as follows,

    Using DTS "Execute SQL Task":

    TRUNCATE TABLE ?
    go

    TRUNCATE TABLE ?
    go

    TRUNCATE TABLE ?
    go

    TRUNCATE TABLE ?
    go

    TRUNCATE TABLE ?
    go
  4. satya Moderator

    There is no quickfix or system supplied SP.

    Take help from this linkhttp://www.sqldts.com/?211 to check source file existence and then control your code to truncate the destination data.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. ChrisFretwell New Member

    We use dts to do what your doing. first step we do is use an active x script to check to see if the file is there. if it is, we continue, if its not, we stop.

    chris

Share This Page