SQL Server Performance

dts help plz

Discussion in 'SQL Server DTS-Related Questions' started by sonijaiswal, Oct 25, 2007.

  1. sonijaiswal New Member

    1.Hi Gurus/satya
    Thankyou for previous help now my present situation is I have to rename some dts packages and other problem is
    i got some 20 dts packages i have to schedule them. the schedule time is same for all the packages.Can i run this
    under a single job or I have to schedule them seperately.
    Sorry but I got other doubt also. I have written a dts package which takes data from a file and load into a table
    it is all automated is their a way to check the existence of file before loading it.
  2. satya Moderator

    You can rename the DTS package using "Transfer Packages Sample Application" here as a
    start point- Tools & Tasks (http://www.sqldts.com/default.aspx?272).
    Further you can schedule relevant DTS package in 1 scheduled job provided if you can define the job steps accordingly, if not you can schedule them individually.
    http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1191424,00.html & http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1244504,00.html for file existence.
  3. zigzag219 New Member

    You can add steps prior to your data loading step that check for existence of files. You will want to add script steps, and then using vbscript you can pass or fail that particular step, which will allow or not allow your next data load step to proceed.
    Check out http://www.sqldts.com/292.aspx for more specific information.
  4. zigzag219 New Member

    Here is a more complete response to your second question...
    Create a Script Task and connect it to your data loading task (whatever type that might be) via an "On Success" workflow pipe. Next, open up the script task and use the following code as it's function replacing "c:myfile.txt" with the file you want to check for. This function will return a success value only if that file exists. It will fail if the file does not. When it fails, the next step (your data loading step) will not be executed.
    I have a screenshot I can email you if you want to see a mockup workflow for this.
    Function Main()
    Dim fso, retval

    retval = DTSTaskExecResult_Failure

    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FileExists("C:myfile.txt") Then
    retval = DTSTaskExecResult_Success
    End If
    Set fso = Nothing

    Main = retval
    End Function

Share This Page