September 24, 2010 at 10:54 pm
markmeiller (5/11/2010)
Good article. I have in the past used a simalar solution with Winscp, however we recently change some of our poliacys and the SSIS packagewas moved to a server where 3rd party apps where forbiden. To get around this i found out that you could easly use the c# ftp class in a script task to do the same thing & it's more relable, and faster cause the server doesn't have to fire up another application.
Do you have an example of this script task that downloads a file instead of uploads a file?
September 25, 2010 at 10:20 am
All you have to do is change "put" to "get."
You're path has to be pointing to a file that actually exists on the remote server, but that's about it.
September 25, 2010 at 10:43 am
Stan Kulp-439977 (9/25/2010)
All you have to do is change "put" to "get."You're path has to be pointing to a file that actually exists on the remote server, but that's about it.
There is no "put" in markmeiller's script task upload. It looks a little more complicated, but I don't think it requires third-party software which is very important for some people.
May 25, 2011 at 3:02 am
To avoid logging in once, add registry entry and use a service account.
Manually add the host key registry entry to [HKEY_USERS\S-1-5-21-3505284427-344095049-820599171-1160\Software\SimonTatham\PuTTY\SshHostKeys] where the SID in this case corresponds to the SQL Server Agent user account.
May 25, 2011 at 9:00 am
edit: wrong thread
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 9, 2011 at 6:43 am
Correction : I mixed up markmeiller's response with the winscp one. markmeiller's ftprequest is a no-go because it doesn't support sftp which is core to the article.
I will stick to the psftp solution, it is fairly well documented and I am not concerned so much about leaving the files behind :
1) I can clean up the files even upon failure of the process, just by separating cleanup in separate process that is next step in the job (both on job failure and success)
2) directory is on a server with very limited access
etc
August 16, 2011 at 9:07 am
Can you, please help me to GET a file from SFTP server? I can PUT a file as your article shows. That works great. I don't have to change to a subdirectory. When I log in to SFTP, I am in the folder where my files sit. So, I changed the ServerSubdirectory to my current directory although I am not sure if that even is required. I then set the SourceFilePath to the name of my file on the server like this: PG335_20110803_29144.xml. I am not using any slashes. I also changed the put into get. The package runs but I don't get the file. I think I am missing a directory where I need to PUT the file on my machine. Please, advise.
Thanks!
Stan
August 16, 2011 at 9:26 am
Dear Stan, if your file is on the sFTP root server, you don't need to change directory. The SourceFilePath is only for PUT activity. GET will download into the directory where psftp.exe is located, with the filename that is same as on server. You CAN use a local directory path to store the file by using the lcd command (local change dir as sopposed to cd which changes dir on the server), but I suggest you first get it working without the added complexity. Also, you can run psftp.exe manually and see what gives. Just open it and respond to the prompts (there is a manual on the putty.org server).
My script file for a get looks like so :
cd SFOLDER
lcd D:\LFOLDER
mget fileprefixfilter*.xml
quit
that's all it takes to download all xml files starting with 'filefilterprefix' from the server folder SFOLDER to the local directory D:\LFOLDER. Later on after ensuring files are valid (ie I didn't download a file that was in the process of being put on the server and incomplete), I then move them to another folder on the sFTP server so they won't be picked up again next time. You may handle that in another way, or just leave files there and use GET instead of MGET if you know the filename you are looking for.
Good Luck, Peter
August 16, 2011 at 9:49 am
Dear Peter, thank you for your reply. I removed the ServerSubdirectory as you suggested since I need to get a file from a root directory. My psftp.exe is sitting in H:\psftp on my machine so getting one file should be very easy. I even placed a MsgBox right after the 'command' being built befor passing it to psftp. Here is how it looks
get PG335_20110803_29144.xml
quit
The SSIS package runs, everything is green but file copied to H:\psftp. Something is wrong here. I will try simply issuing the same command within the psftp.exe manually.
Stan
August 16, 2011 at 10:13 am
if you want to copy to another folder, you need to add a line :
lcd H:\LCL\FOLDER\NAME
get PG335_20110803_29144.xml
quit
that's "LCD"
August 16, 2011 at 10:23 am
Peter, thank you. I actually don't need to change a directory. I might want to do it in the future but for now a directory where psftp.ext is sitting is fine.
I ran the same command I generat inside the code but manually by opening the psftp and I was able to copy the file.
Since I was able to copy a file from my machine to the server, I don't think the problem is with the credentials/accounts used (me vs. SSIS). I am stumled upon this really.....
August 16, 2011 at 10:45 am
This is very weird but when I added lcd and created a subfolder within the folder that contains the psftp.exe, I was able to copy the file! I tried wildcard to copy all .xml files and that did not work.... Hmmm, at least copying one file works.
Thank you, Peter!
August 16, 2011 at 11:31 am
for the wildcard you need to use mget instead of get (as per putty docs)
August 16, 2011 at 12:23 pm
Yes, I just figured that out. Thank you!
May 17, 2012 at 1:11 pm
Hello,
I have read the article and the discussion with interest. I've tried to build and run a package that moves a file on our company's network to a test SFTP location.
I've attached an email task to the OnError event. I'm not getting any errors via email or when I see the window when I am running the package.
Unfortunately, the package hasn't yet worked. I'm wondering if that is because putty needs to have a private key specified. (When I connect via WinSCP to see if the file is where I am trying to put it, I need to specify the private key.)
If the private key is the problem, how would I implement that in the original solution?
thanks,
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply