February 14, 2008 at 10:18 am
I have a DTS that would check if a file is present, and if it is then process it and do a SQL import into the database, else exit out of the loop.
I am aware that I can use the success and failure in VBScript, but in the case when a file is not present, I do not want the DTS return a failure as that would kick off an alert.
It must report an error only if there is an error when the file is found and error occured.
Please help.
February 14, 2008 at 10:44 am
Add an Active X Script task before your Data Transformation task that does the following:
1) Disable the Data Transformation task
2) Uses the FileSystemObject to determine if the file exists
3) Enables the Data Transformation if FileSystemObject.FileExists is true.
Here's a code example, which assumes that your data transformation is DTSStep_DTSDataPumpTask_1.
Function Main()
Dim objTransformation
Dim objFSO
' disable data transfomation task
Set objTransformation = DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPumpTask_1")
objTransformation.DisableStep = True
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists("C:\FileToImport.txt") Then
objTransformation.DisableStep = False
End If
Set objTransformation = Nothing
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
The Data Transformation task will not execute if the file doesn't exist.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply