SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


No SFTP Task Component in SSIS 2005/2008? No Problem!


No SFTP Task Component in SSIS 2005/2008? No Problem!

Author
Message
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2720 Visits: 2204
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?
Stan Kulp-439977
Stan Kulp-439977
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1028 Visits: 1127
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.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2720 Visits: 2204
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.
Nikhil Shikarkhane
Nikhil Shikarkhane
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33146 Visits: 18560
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

Peter De Ceulaer
Peter De Ceulaer
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
suslikovich
suslikovich
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 300
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
Peter De Ceulaer
Peter De Ceulaer
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
suslikovich
suslikovich
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 300
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
Peter De Ceulaer
Peter De Ceulaer
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search