Hello All,

I have gained much from SQLDTS.com but this looping thing is driving me
nuts. I have included the scripts below.

The problem is that I loop back to the point I want to when the package
completes the first time but it stops and does not execute the Transform Data
task it just stops. I have additional files but it just stops at the
transform data task. I tried the example 246 from sqldts and it helped
somewhat but not completely.



Can you help?



Thanks,

Larry



First Script:

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

' Visual Basic ActiveX Script

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

OPTION EXPLICIT



Function Main()



' Object variables used from inside the script

dim objFSO, objFolder, objFile, colFiles, icount, blnLoop



icount = 0

blnLoop ="false"

DTSGlobalVariables("PKG_Loop").Value = blnLoop



DTSGlobalVariables("FileName").Value = ""



'Variables used to hold DTSGlobalVariables

dim sSourceDir, sProcessDir, sHistoryDir, sfilePrefix, sfileSuffix,
intPrefixLen



sSourceDir = DTSGlobalVariables("SourceDir").Value

sProcessDir = DTSGlobalVariables("ProcessDir").Value

sHistoryDir = DTSGlobalVariables("HistoryDir").Value

sfilePrefix = DTSGlobalVariables("FilePrefix").Value

sfileSuffix = DTSGlobalVariables("FileSuffix").Value

intPrefixLen = len(DTSGlobalVariables("FilePrefix").Value)



set objFSO = CreateObject("Scripting.FileSystemObject")

set objFolder = objFSO.GetFolder(sSourceDir)

set colFiles = objFolder.Files



If colFiles.count > 0 Then



' Get the count of files matching the package specifications.

For each objFile in colFiles



If Mid(objFile.Name,1,intPrefixLen) = sfilePrefix AND
Right(objFile.Name, 4) = sFileSuffix THEN

icount= icount+1

blnLoop = "true"

End If

DTSGlobalVariables("FileCount").Value = icount

DTSGlobalVariables("PKG_Loop").Value = blnLoop

Next



For each objFile in colFiles

If Mid(objFile.Name,1,intPrefixLen) = sfilePrefix AND
Right(objFile.Name, 4) = sFileSuffix THEN

DTSGlobalVariables("FileName").Value = sfilePrefix &
sFileSuffix

If objFSO.FolderExists(sProcessDir) then

objFile.Move(sProcessDir & "\" & sfilePrefix &
sFileSuffix)

Else

objFSO.CreateFolder(sProcessDir)

objFile.Move(sProcessDir & "\" & sfilePrefix &
sFileSuffix)

End If

Exit For

End if



Next

End If



msgbox DTSGlobalVariables("FileCount").Value & " " &
DTSGlobalVariables("PKG_Loop").Value

Set objFSO = Nothing

Set objFolder = Nothing

Set colFiles = Nothing



Main = DTSTaskExecResult_Success





End Function



Second Script:

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

' Visual Basic ActiveX Script

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

Function Main()



' Object variables used from inside the script

dim objFSO, objFolder, objFile, colFiles, strYear, strMonth, strDay,
strHour, strMin, strSec, dtDate, dtTime





' Get current year

strYear = DatePart("yyyy",Date)



' Get current month, add leading zero if necessary

If DatePart("m",Date) < 10 Then

strMonth = 0 & DatePart("m",Date)

Else

strMonth = DatePart("m",Date)

End If



' Get current day, add leading zero if necessary

If DatePart("d",Date) < 10 Then

strDay = 0 & DatePart("d",Date)

Else

strDay = DatePart("d",Date)

End If



' Get current hour, add leading zero if necessary

If DatePart("h",Time) < 10 Then

strHour = 0 & DatePart("h",Time)

Else

strHour = DatePart("h",Time)

End If



' Get current minute, add leading zero if necessary

If DatePart("n",Time) < 10 Then

strMin = 0 & DatePart("n",Time)

Else

strMin = DatePart("n",Time)

End If



' Get current seconds, add leading zero if necessary

If DatePart("s",Time) < 10 Then

strSec = 0 & DatePart("s",Time)

Else

strSec = DatePart("s",Time)

End If



dtDate = strYear & strMonth & strDay

dtTime = strHour & strMin & strSec



'Variables used to hold DTSGlobalVariables

dim sSourceDir, sProcessDir, sHistoryDir, sfilePrefix, sfileSuffix,
intPrefixLen



sSourceDir = DTSGlobalVariables("SourceDir").Value

sProcessDir = DTSGlobalVariables("ProcessDir").Value

sHistoryDir = DTSGlobalVariables("HistoryDir").Value

sfilePrefix = DTSGlobalVariables("FilePrefix").Value

sfileSuffix = DTSGlobalVariables("FileSuffix").Value

intPrefixLen = len(DTSGlobalVariables("FilePrefix").Value)



set objFSO = CreateObject("Scripting.FileSystemObject")

set objFolder = objFSO.GetFolder(sProcessDir)

set colFiles = objFolder.Files



For each objFile in colFiles

If objFSO.FolderExists(sHistoryDir) then

objFile.Move(sHistoryDir & "\" & sfilePrefix & dtDate &
dtTime & sFileSuffix)

DTSGlobalVariables("FileName").Value = sfilePrefix & dtDate
& dtTime & sFileSuffix



Else

objFSO.CreateFolder(sHistoryDir)

objFile.Move(sHistoryDir & "\" & sfilePrefix & dtDate &
dtTime & sFileSuffix)



End If

'Exit For

Next



Set objFSO = Nothing

Set objFolder = Nothing

Set colFiles = Nothing

Main = DTSTaskExecResult_Success

End Function



Third Script:

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

' Visual Basic ActiveX Script

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



Function Main()

dim pkg, stpLoop



set pkg = DTSGlobalVariables.Parent

set stpLoop = pkg.Steps("DTSStep_DTSActiveScriptTask_1")

if DTSGlobalVariables("PKG_Loop") = "true" then

msgbox "Loop says true"

stpLoop.DisableStep = False

stpLoop.ExecutionStatus = DTSStepExecStat_Waiting



end if

Main = DTSTaskExecResult_Success

End Function