April 20, 2006 at 10:17 am
Hi Guys,
I have a dts package that download zip files from a ftp directory. for this I have used the "SQLDTS File Transfer Protocol Task" (http://www.sqldts.com/default.aspx?302). This task has the option to "Write FTP Files collection of copied files to global variable for later use". This creates a custom collection with a collection of all the downloaded files. Once this has completed I want to unzip all the files using Info-Zip UnZip one at a time. So a loop. Once the file has been unzipped it must bulk insert the file into a specified table using the "Bulk Insert Task" then move the archive file to an archive directory and extract the next archive file. My problem is that "Custom Collection" doesn't support BOF and EOF. So I assume you have to "ForEach x IN y next". But I do not know how to do this.
Here is my current workflow activex script residing on the "Execute Process Task".
Option Explicit
Function Main()
Dim objPkg
Dim objFTPFiles
Set objPkg = DTSGlobalVariables.Parent
Set objFTPFiles = DTSGlobalVariables("gvArchiveFileNames").Value
If Not objFTPFiles.EOF And Not objFTPFiles.BOF Then
DTSGlobalVariables("gvArchiveFileName").Value = objFTPFiles.Fields(0).Value
With objPkg.Tasks("DTSTask_DTSCreateProcessTask_1")
.Properties("ProcessCommandLine").Value = DTSGlobalVariables("gvUnzipPathname").Value & " -o -j " & DTSGlobalVariables("gvArchiveFileName").Value & " -d " & DTSGlobalVariables("gvExtractFolder").Value
End With
objFTPFiles.MoveNext
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
Set objFTPFiles = Nothing
Set objPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Here is my activex script after the process to to reloop.
Function Main()
Set objFTPFiles = DTSGlobalVariables("gvArchiveFileNames").Value
If objFTPFiles.EOF Then
Main = DTSStepScriptResult_ExecuteTask
Else
DTSGlobalVariables.Parent.Steps("DTSStep_DTSCreateProcessTask_1").ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSStepScriptResult_DontExecuteTask
End If
Set objFTPFiles = Nothing
End Function
Is there a way to convert a "Custom Collection" to a recordset so that BOF and EOF can be used or how do I loop through the collection using foreach?
Thanks in advance...
Hank
April 21, 2006 at 12:27 pm
Could you extract to a working folder then process each file in the loop and move/delete the file at the end of the loop? Seems like that would be easier if you're already doing the loop in the first place. I've used that before pretty well.
1. Download files
2. Unzip to "ToProcess" folder
2a. Move/Delete Zip files
3. Loop through all files in ToProcess
3a. Import data
3b. Move/Delete file after import or after steps in processing (depends on business rules)
3c. Go to next file.
-Pete
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply