Preventing SQL Server DTS package failures
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.
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.
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
Main = DTSStepScriptResult_DontExecuteTask
Set oFSO = Nothing
This was first published in June 2006