July 26, 2006 at 2:40 pm
I tried a For Loop and Do until EOF. Here is what I am trying to do: I am looping through a table that contains paths to access dbs. For each loop I run a transformation for each db. Everything works fine unless one of the access files is not there. I have an of statement to catch that scenerio. Right now it just has a msgbox , I will turn that into an email later. Problem is in the For Loop when it skips that record I get an error on EOF when it tries to get the next record since I got it in the loop. When I do the Do until EOF it breals out of the loop completely. I want this loop to loop through the list and skip over the not found path but STILL keep going through the rest. Most likely it is something really easy. I just need another set of eyes on it. any help would be appreciated. Here is my ActiveX Task. PS, if there is a better way to do this entirely I am open for suggestions.
Option Explicit
Function Main()
Dim oRS, oPkg, oConn, iLoop, fso
Set oRS = DTSGlobalVariables("Path").Value
Set oPkg = DTSGlobalVariables.Parent
Set fso = CreateObject("Scripting.FileSystemObject")
Do until oRS.EOF
'For iLoop = 0 to oRS.RecordCount -1
DTSGlobalVariables("Path").Value = oRS.Fields(0).Value
'Check to see if file is there
If fso.FileExists(DTSGlobalVariables("Path")) = FALSE Then
MsgBox "File is NOT there"
oRS.MoveNext
DTSGlobalVariables("Path").Value = oRS.Fields(0).Value
End If
'Truncate
oPkg.Steps("DTSStep_DTSExecuteSQLTask_3").Execute
' Set Datasource
Set oConn = oPkg.Connections("Microsoft Access")
oConn.DataSource = DTSGlobalVariables("Path").Value
'Transformation
oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting
oPkg.Steps("DTSStep_DTSDataPumpTask_1").Execute
'Hack to Close connection
oPkg.Steps("DTSStep_DTSExecuteSQLTask_5"). Execute
'Insert Data via SP
oPkg.Steps("DTSStep_DTSExecuteSQLTask_2"). Execute
' Move to the next row in preparation for loop iteration
oRS.MoveNext
Loop
'Next
Set oRS = Nothing
Set oPkg=Nothing
Main = DTSTaskExecResult_Success
End Function
July 26, 2006 at 3:42 pm
Looks to me like oRS.MoveNext is being executed twice when you come to a file that doesn't exist. Try this:
Dim oRS, oPkg, oConn, iLoop, fso
Set oRS = DTSGlobalVariables("Path").Value
Set oPkg = DTSGlobalVariables.Parent
Set fso = CreateObject("Scripting.FileSystemObject")
Do until oRS.EOF
DTSGlobalVariables("Path").Value = oRS.Fields(0).Value
'Check to see if file is there
If fso.FileExists(DTSGlobalVariables("Path")) = FALSE Then
MsgBox "File is NOT there"
Else
'Truncate
oPkg.Steps("DTSStep_DTSExecuteSQLTask_3").Execute
' Set Datasource
Set oConn = oPkg.Connections("Microsoft Access")
oConn.DataSource = DTSGlobalVariables("Path").Value
' Transformation
oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting
oPkg.Steps("DTSStep_DTSDataPumpTask_1").Execute
' Hack to Close connection
oPkg.Steps("DTSStep_DTSExecuteSQLTask_5"). Execute
' Insert Data via SP
oPkg.Steps("DTSStep_DTSExecuteSQLTask_2"). Execute
End If
' Move to the next row in preparation for loop iteration
oRS.MoveNext
Loop
Set oRS = Nothing
Set oPkg = Nothing
Set fso = Nothing
Set oConn = Nothing
Main = DTSTaskExecResult_Success
July 27, 2006 at 12:53 pm
Change the structure of the IF statement so that the rest of the processing is only done when the file exists. Note that the MoveNext and Path assignment are moved outside the if. You will always move to the next record regardless of the existance of the file. Since the path assignment is always done at the top of the file you don't need it in the if statement either.
Function Main()
Dim oRS, oPkg, oConn, iLoop, fso
Set oRS = DTSGlobalVariables("Path").Value
Set oPkg = DTSGlobalVariables.Parent
Set fso = CreateObject("Scripting.FileSystemObject")
Do until oRS.EOF
'For iLoop = 0 to oRS.RecordCount -1
DTSGlobalVariables("Path").Value = oRS.Fields(0).Value
'Check to see if file is there
If fso.FileExists(DTSGlobalVariables("Path")) = FALSE Then
MsgBox "File is NOT there"
Else
'Truncate
oPkg.Steps("DTSStep_DTSExecuteSQLTask_3").Execute
' Set Datasource
Set oConn = oPkg.Connections("Microsoft Access")
oConn.DataSource = DTSGlobalVariables("Path").Value
'Transformation
oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting
oPkg.Steps("DTSStep_DTSDataPumpTask_1").Execute
'Hack to Close connection
oPkg.Steps("DTSStep_DTSExecuteSQLTask_5"). Execute
'Insert Data via SP
oPkg.Steps("DTSStep_DTSExecuteSQLTask_2"). Execute
End If
' Move to the next row in preparation for loop iteration
oRS.MoveNext
Loop
'Next
Set oRS = Nothing
Set oPkg=Nothing
Main = DTSTaskExecResult_Success
End Function
July 27, 2006 at 1:28 pm
Thanks guys. You both gave me ideas that worked. Now I only have one last conquest....
I am having troubles using xp_sendmail to create the e-mail message with the Path Variable. Any suggestions how to construct?
Here is my code:
Option Explicit
Function Main()
Dim oRS, oPkg, oConn, cnt, fso, objCN, objCMD
set oRS = CreateObject("ADODB.Recordset")
set oRS = DTSGlobalVariables("Path").value
set oPkg = DTSGlobalVariables.Parent
set fso = CreateObject("Scripting.FileSystemObject")
for cnt = 1 to oRS.RecordCount
DTSGlobalVariables("Path").Value = oRS.Fields(0).Value
'Check to see if file is there
If fso.FileExists(DTSGlobalVariables("Path")) = True Then
'Truncate
oPkg.Steps("DTSStep_DTSExecuteSQLTask_3").Execute
' Hack to Close connection
oPkg.Steps("DTSStep_DTSExecuteSQLTask_5"). Execute
' Set Datasource
Set oConn = oPkg.Connections("Microsoft Access")
oConn.DataSource = DTSGlobalVariables("Path").Value
' Transformation
oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = False
oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting
oPkg.Steps("DTSStep_DTSDataPumpTask_1").Execute
oPkg.Steps("DTSStep_DTSDataPumpTask_1").DisableStep = True
' Hack to Close connection
oPkg.Steps("DTSStep_DTSExecuteSQLTask_5"). Execute
' Insert Data via SP
oPkg.Steps("DTSStep_DTSExecuteSQLTask_2"). Execute
'Email If Path is not found
Else
set objCN = CreateObject("ADODB.Connection")
set objCMD = CreateObject("ADODB.Command")
objCN.open = "Provider=sqloledb;Data Source=FoBar;Initial Catalog=FooBar;Integrated Security=SSPI;"
objCMD.ActiveConnection = objCN
objCMD.CommandText = "exec master.dbo.xp_smtp_sendmail @FROM= N'STLSQL01',@TO= N'jason.cohn@libhamp.com',@priority= N'HIGH',"
objCMD.CommandText = objCMD.CommandText & "@subject= N'DTS Package Failure (Access Path Not Found)',@type= N'text/plain',"
objCMD.CommandText = objCMD.CommandText & "@message= DTSGlobalVariables("Path").Value "
objCMD.CommandText = objCMD.CommandText & ",@timeout= 10000, @server= N'CHIMAIL01' "
MsgBox objCMD
'objCMD.Execute
Set objCN = Nothing
Set objCMD = Nothing
End If
oRS.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
July 27, 2006 at 1:31 pm
change the expression
objCMD.CommandText = objCMD.CommandText & "@message= DTSGlobalVariables("Path").Value "
to look like
objCMD.CommandText = objCMD.CommandText & "@message= " + DTSGlobalVariables("Path").Value
July 27, 2006 at 1:59 pm
That is close. I get an error on the objCMD.Execute line that executes the sp.
Error is Incorrect syntax nere 'K:'
The "PAth" Variable = K:\Database\BtPers\GretchenS\btisdef.mdb
Does it not like the \ character?
July 27, 2006 at 2:04 pm
oops left off a bunch of those pesky quote marks
objCMD.CommandText = objCMD.CommandText & "@message= "" " + DTSGlobalVariables("Path").Value + """"
July 27, 2006 at 2:10 pm
got it! Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply