FTP and Unzipping in SSIS
-
Here's a challenge for all of you.
We are currently developing to pull data from numerous locations in Europe and Asia. They use a non compatible database system (AIDA), so they current plan is for them to export to a deliminated file then zip them so we can ftp them back to the US.
The warehouse part is nice and easy. Blow away what's there, replace with what's in the files. (Better even that what we did in Class Brian!
) Where I'm having issues is how to make SSIS FTP and then unzip.Any ideas?
C'Anne (Ches) Weldishofer
Clear Channel
-
This was removed by the editor as SPAM
-
I'll try and get a peak at my colleagues code. The good news is he is doing exactly what you are doing:
He FTP's a file from a remote locations, and then unzips it locally. Finally, he takes the metadata about the file locations and outputs it (to a test file, iirc).
It will be fairly straight-forward stuff (i.e. not necessarily the best method). IIRC, he uses execute process to run the unzip as a commandline. I think he is using activeX to check for existance of files, and then builds up an ftp command file (c.md or bat), and executes it.
HTH
-
Thank you Wanderer, I look forward to seeing it.
I've been toying with getting one of our app guys to write a web service that checks for their file, ftp's and unzips, then we just have to hit that and copy from the unzipped files.
-
As I expected. He is using an ActiveX script to generate a CMD file. The ActiveX is pulling in the variables defined in the Package to get userid, password, IP address etc,., and so he creates the FTP command line file on the fly (i.e. we don't have a text file lying around with userid, locations and password in clear text). This file is deleted at the end of the package.
After generating the FTP file, the next task is a execute process task that executes the cmd file, thus invoking the ftp. He is not using the FTP task that SSIS provides.
After completion of the FTP file (since he has set up precendence constraints, this works), he run's commandline unzips:
EXECUTABLE: C:\Program Files\WinZip\wzunzip.exe
ARGUEMENTS: -d -o "UNC Source location" "UNC Target location"
That "UNC Source location" was where the FTP dropped the files.
The activeX (scrubbed to protect the allegedly innocent 🙂 ). I claim no rights or responsibility - this is a colleagues code.
-----------------code---------------
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim fso, f, c, passwrd, user, IPAddr, filepath, fileName
passwrd = DTSGlobalVariables("GVFTPPassword")
user = DTSGlobalVariables("GVFTPUser")
ipaddr = DTSGlobalVariables("GVFTPIP")
BatchPath = DTSGlobalVariables("GVBatchFilePath")
set fso = CreateObject("Scripting.FileSystemObject")
set f = fso.OpenTextFile(BatchPath & "\afile.ini", 2, True)
f.WriteLine(user)
f.WriteLine(passwrd)
f.WriteLine("binary")
f.WriteLine("cd aDirectory")
f.WriteLine("get MyZipFile.zip")
f.WriteLine("bye")
f.Close()
set fso = CreateObject("Scripting.FileSystemObject")
set f = fso.OpenTextFile(BatchPath & "\anotherFile.cmd", 2, True)
f.WriteLine("@echo off")
f.WriteLine("UNCTargetLocation")
f.WriteLine("ftp -v -i -s:aFile.ini " & IPAddr)
f.Close()
set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
-----------------code---------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply