DTS errors but it works, annoying though | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS errors but it works, annoying though

Ok guys, I wrote a DTS package and gets ran every night. When it finds the file, it processes the file. When it cannot find the file, it sends us an email saying file was not found. It’s working. However, it was just today that we noticed in history log that when there is no input file, the error below is what is recorded: ————————error begin————————-
Executed as user: USSQLEXEC. DTSRun: Loading… DTSRun: Executing… DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
————————error end—————————-
Here is my code: ‘**********************************************************************
‘ Visual Basic ActiveX Script
‘************************************************************************ Function Main()
Dim fso
Dim sourceFile
Dim objFSO
Dim objFile
Dim strLine
Dim strNewContents
Dim o_Package1
Dim o_Task1 Set o_Package1 = DTSGlobalVariables.Parent
Set o_Task1= o_Package1.Tasks("DTSTask_DTSExecuteSQLTask_2").Customtask sourceFile = "F:payoffpayoffdate" & Right(Year(Now),2) & Right("0" & Month(Now),2) & Right("0" & Day(Now),2) & ".txt" Const ForReading = 1
Const ForWriting = 2 Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(sourceFile) Then Set objFile = objFSO.OpenTextFile(sourceFile, ForReading) Do Until objFile.AtEndOfStream
strLine = objFile.Readline
strLine = Trim(strLine)
strline = replace(strline,chr(9),"")
If Len(strLine) > 0 Then
strNewContents = strNewContents & strLine & vbCrLf
End If
Loop objFile.Close Set objFile = objFSO.OpenTextFile(sourceFile, ForWriting)
objFile.Write strNewContents
objFile.Close
set objFso = nothing
set objFile = nothing
End If DTSGlobalVariables("fileName").Value = sourceFile
Set fso = CreateObject("Scripting.FileSystemObject") If fso.FileExists(sourceFile) Then
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections(1)
oConn.datasource = DTSGlobalVariables("fileName").Value
o_Task1.SQLStatement= Replace(o_Task1.SQLStatement, "<FILENAME>", DTSGlobalVariables("fileName").Value)
set oPackage = nothing
set oConn = nothing
set fso = nothing
Main = DTSTaskExecResult_Success
Else
set fso = nothing
Main = DTSTaskExecResult_Failure
End If End Function Please let me know how to totally get rid of that error. Thanks!
try with some error hadling stuff. —————————————-
]]>