SQL DTS loop error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQL DTS loop error

Author

Topic

jeromet
Starting Member

2 Posts

Posted - 2005-01-25 : 19:46:07

I have a SQL DTS that imports a file and does a loop to get the next file. It works fine with the first file but get an error on the second file. The error is "cannot open data file, cannot find file"on the transfom data pump task. I know it can find the find since the activex script msgbox displays the correct file path & name. I believe the error is with the disable step and/or execution status because I can get the same error on an activex script task by changing the disable step status or execuion status. I hope you can help, any ideas. Thanks, Jerry T.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 20:00:28

You haven't posted the code so I'm not sure how we can help you.

Tara

jeromet
Starting Member

2 Posts

Posted - 2005-01-25 : 20:06:10

Here is the GET FILE Activex Script:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()

' Declare FSO Related Variables
Dim sRecvFolder
Dim sRecvFileName
Dim sRecvFileBatch
Dim sHistFolder
Dim sRecvFileFound
Dim sSendACKNFile
Dim sSendFolder
Dim sModDate
Dim fso
Dim fsoFolder
Dim fsoFile
Dim fsoHistFolder
Dim fsoFolderSend

'Declare Variables
Dim oPKG
Dim oConnection
dim stpEnterLoop
dim stpFinished

' Import Folder
sSendFolder = DTSGlobalVariables("gvSendFolder")

sRecvFolder = DTSGlobalVariables("gvRecvFolder")
sHistFolder = DTSGlobalVariables("gvHistFolder")
sModDate = FormatDateTime(Now,2)

DTSGlobalVariables("gvModDate").value = sModDate

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sRecvFolder)
Set fsoHistFolder = fso.GetFolder(sHistFolder)
Set fsoFolderSend = fso.GetFolder(sSendFolder)
Set oPKG = DTSGlobalVariables.Parent

sRecvFileFound = "NO"
'************************************************************************
MSGBOX "START"
Msgbox oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_5").ExecutionStatus

For Each fsoFile in fsoFolder.Files
sRecvFileFound = "NO"
' Get first filename
sRecvFileName = fsoFile.Name
DTSGlobalVariables("gvRecvFileName").value = sRecvFileName
MsgBox sRecvFolder&sRecvFileName

If Ucase (Left(sRecvFileName,4)) = "INVD" and fso.getfile(sRecvFolder & sRecvFileName).Size>0 Then
fso.CopyFile sRecvFolder & sRecvFileName, sHistFolder & sRecvFileName
sRecvFileBatch = Mid(sRecvFileName, 6, (InStr(6,sRecvFileName, ".")-6))
DTSGlobalVariables("gvRecvFileBatch").value = sRecvFileBatch
sRecvFileFound = "YES"

' Get Package Object
Set oPKG = DTSGlobalVariables.Parent

' Get Source Connection Object
Set oConnection = oPKG.Connections("Connect1")

' Set new Filename
oConnection.DataSource =sRecvFolder&sRecvFileName

oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting
' oPkg.Steps("DTSStep_DTSDataPumpTask_1").Execute

oPkg.Steps("DTSStep_DTSActiveScriptTask_3").DisableStep = False
oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus = DTSStepExecStat_Waiting

' oPkg.Steps("DTSStep_DTSActiveScriptTask_5").DisableStep = True
oPkg.Steps("DTSStep_DTSActiveScriptTask_5").ExecutionStatus = DTSStepExecStat_Inactive

MsgBox "file found"
Msgbox oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_5").ExecutionStatus

Exit For
End If
'************************************************************************

If Ucase (Left(sRecvFileName,4)) = "INVD" and fso.getfile(sRecvFolder & sRecvFileName).Size=0 Then
'Msgbox "INVD delt"
sRecvFileFound = "ZERO"
fso.CopyFile sRecvFolder & sRecvFileName, sHistFolder & sRecvFileName
sRecvFileBatch = Mid(sRecvFileName, 6, (InStr(6,sRecvFileName, ".")-6))
DTSGlobalVariables("gvRecvFileBatch").value = sRecvFileBatch
fso.DeleteFile sRecvFolder & sRecvFileName
sSendACKNFile = "ACKN_"&sRecvFileBatch&".txt"
fso.CreateTextFile sSendFolder&sSendACKNFile
fso.CopyFile sSendFolder&sSendACKNFile, sHistFolder & sSendACKNFile

Set oPKG = DTSGlobalVariables.Parent

oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
' oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Inactive

oPkg.Steps("DTSStep_DTSActiveScriptTask_3").DisableStep = False
oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus = DTSStepExecStat_Waiting
oPkg.Steps("DTSStep_DTSActiveScriptTask_3").Execute

oPkg.Steps("DTSStep_DTSActiveScriptTask_5").DisableStep = False
oPkg.Steps("DTSStep_DTSActiveScriptTask_5").ExecutionStatus = DTSStepExecStat_Inactive

Exit For
End If
'************************************************************************
'Msgbox "next"
Next

'************************************************************************

If (sRecvFileFound) = "NO" Then
' Get Package Object
'MsgBox "file NOT found"
Set oPKG = DTSGlobalVariables.Parent

oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = True
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Inactive

oPkg.Steps("DTSStep_DTSActiveScriptTask_3").DisableStep = True
oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus = DTSStepExecStat_Inactive

oPkg.Steps("DTSStep_DTSActiveScriptTask_5").DisableStep = False
oPkg.Steps("DTSStep_DTSActiveScriptTask_5").ExecutionStatus = DTSStepExecStat_Waiting

End If
'************************************************************************

Main = DTSTaskExecResult_Success
End Function

Here is the LOOP ActiveX Script Task:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim oPkg
Dim oStep

MsgBox "Loop "

' Get reference to the Package
Set oPkg = DTSGlobalVariables.Parent

' Get reference to the step at the start of the loop
'' Set oStep = oPkg.Steps("DTSStep_DTSActiveScriptTask_1")

' Set the status back to waiting
'' oStep.ExecutionStatus = DTSStepExecStat_Waiting

oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting

oPkg.Steps("DTSStep_DTSActiveScriptTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus = DTSStepExecStat_Waiting

oPkg.Steps("DTSStep_DTSActiveScriptTask_3").DisableStep = False
oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus = DTSStepExecStat_Waiting

Msgbox oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_3").ExecutionStatus
Msgbox oPkg.Steps("DTSStep_DTSActiveScriptTask_5").ExecutionStatus

oPkg.Steps("DTSStep_DTSActiveScriptTask_1").Execute

' Clean Up

' Set oStep = Nothing
' Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources