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

  • UMG Developer

    SSChampion

    Points: 13482

    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

    SSCrazy Eights

    Points: 9978

    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

    SSChampion

    Points: 13482

    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

    Default port

    Points: 1478

    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

    SSC Guru

    Points: 281243

    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

  • Peter De Ceulaer

    SSC Enthusiast

    Points: 101

    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

    Old Hand

    Points: 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

    SSC Enthusiast

    Points: 101

    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

    Old Hand

    Points: 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

    SSC Enthusiast

    Points: 101

    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"

  • suslikovich

    Old Hand

    Points: 300

    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.....

  • suslikovich

    Old Hand

    Points: 300

    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!

  • Peter De Ceulaer

    SSC Enthusiast

    Points: 101

    for the wildcard you need to use mget instead of get (as per putty docs)

  • suslikovich

    Old Hand

    Points: 300

    Yes, I just figured that out. Thank you!

  • cafescott

    SSCarpal Tunnel

    Points: 4724

    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 61 total)

You must be logged in to reply to this topic. Login to reply