Click here to monitor SSC
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
suslikovich
suslikovich
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 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
suslikovich
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 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
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
for the wildcard you need to use mget instead of get (as per putty docs)
suslikovich
suslikovich
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 300
Yes, I just figured that out. Thank you!
cafescott
cafescott
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 733
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,
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
I've dropped the putty solution in favor of ssissftp.codeplex.com, a custom package that has worked very well for me.
angel_mata
angel_mata
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 235
Hi Mark,
Great post on the C# script. I'm a complete newbie with this. Do you have a download version of the script?
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
I have just written an article on an SFTP open source custom task component that works much more reliably than this method:

"SFTP, encrypt or compress data files in SSIS using custom components"
khicon73
khicon73
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 24
I download the 'SftpScriptTaskDemo.dtsx', also download 'psftp.exe' and placed it in 'C:\psftp\psftp.exe', and imported into my SSIS project. I modified the SFTP's credentials...It run fine, no error but i didn't see any file upload to sftp site. what did i do wrong? Please help.
Thank you so much.

I know it's nothing wrong with the credentials because i can manual transfer the files to that server.

1st script i modifed below, and i haven't touch anything on 2nd script.
Public Sub Main()

'Load global variables with values
Dts.Variables("PathToBatch_bat").Value = "c:\psftp\psftp.bat"
Dts.Variables("PathToPsftp_exe").Value = "c:\psftp\psftp.exe"
Dts.Variables("PathToScript_sc").Value = "c:\psftp\script.sc"
Dts.Variables("ServerName").Value = "sftp.server_name.domain"
Dts.Variables("ServerUserName").Value = "username"
Dts.Variables("ServerPassword").Value = "password"
Dts.Variables("ServerSubdirectory").Value = "/sftp"
Dts.Variables("SourceSubdirectory").Value = "C:\psftp\Test.csv"

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

Please help, i'm very appreciated.
Thanks again.
Attachments
sql1.png (1 view, 16.00 KB)
sql2.png (0 views, 8.00 KB)
vonda.ackerman
vonda.ackerman
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 70
I have had great success on using this many times. You may want to make the following change and try it.
Also make sure that you put your variables in the ReadWriteVaraibles on your script task.



Public Sub Main()

'Load global variables with values
Dts.Variables("PathToBatch_bat").Value = "c:\psftp\psftp.bat"
Dts.Variables("PathToPsftp_exe").Value = "c:\psftp\psftp.exe"
Dts.Variables("PathToScript_sc").Value = "c:\psftp\script.sc"
Dts.Variables("ServerName").Value = "sftp.server_name.domain"
Dts.Variables("ServerUserName").Value = "username"
Dts.Variables("ServerPassword").Value = "password"
Dts.Variables("ServerSubdirectory").Value = "/sftp" --change this to "sftp" (if root folder, just leave as ""
Dts.Variables("SourceSubdirectory").Value = "C:\psftp\Test.csv"

Dts.TaskResult = ScriptResults.Success

End Sub

End Class
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