SQLDTS File Transfer Protocol Task and unzipping

  • 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

  • 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