Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

No SFTP Task Component in SSIS 2005/2008? No Problem! Expand / Collapse
Author
Message
Posted Friday, September 24, 2010 10:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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?
Post #993195
Posted Saturday, September 25, 2010 10:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 123, Visits: 884
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.
Post #993273
Posted Saturday, September 25, 2010 10:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #993274
Posted Wednesday, May 25, 2011 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2011 11:54 AM
Points: 154, Visits: 203
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.
Post #1114578
Posted Wednesday, May 25, 2011 9:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:55 PM
Points: 21,204, Visits: 14,889
edit: wrong thread



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1114798
Posted Tuesday, August 9, 2011 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 1:25 PM
Points: 9, Visits: 64
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
Post #1156778
Posted Tuesday, August 16, 2011 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:49 PM
Points: 13, Visits: 265
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
Post #1160602
Posted Tuesday, August 16, 2011 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 1:25 PM
Points: 9, Visits: 64
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
Post #1160611
Posted Tuesday, August 16, 2011 9:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:49 PM
Points: 13, Visits: 265
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
Post #1160628
Posted Tuesday, August 16, 2011 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 1:25 PM
Points: 9, Visits: 64
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"
Post #1160650
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse