How can I make a job successful when it contains a DTS package that transfers data from a text file to a SQL Server table and updates the table? The problem is the job should run daily but the file may not always be there.

    Requires Free Membership to View

If I understand correctly, you want your DTS package not to fail just because your text file does not exist. You may consider working with files and the FileSystemObject and ActiveX Script Tasks. Have your DTS package include an ActiveX Script task that verifies the file exists. If the file does not exist, the DTS package will be done. If the file does exist an 'On Success' flow executes your data pump transferring the data from it to the SQL Server tables.
In the following example, the file name is stored in a global variable.
Function Main()
        Dim oFSO, sFileName

        ' Get the name of the file from the global variable "ImportFileName"
        sFilename = DTSGlobalVariables("ImportFileName").Value

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Main = DTSStepScriptResult_ExecuteTask
        Else
                Main = DTSStepScriptResult_DontExecuteTask
        End If

        Set oFSO = Nothing
End Function

This was first published in June 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: