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.
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation