February 28, 2018 at 1:41 pm
Hi,
I have been looking all over the web for this. I realize that SSIS does not, on its own handle SFTP. A number of sites suggest using WinSCP, but have not gives a full explanation of how to code for this. Maybe its me but they seem to leave things out. Like one site did that I need a FingerPrint variable, but did not explain what the was; I found that out on another site, but then that left out other things.
Does anyone know of a good site for this or maybe you might have a better way; I sure would be open to anything at this point.
Thank you
March 1, 2018 at 1:21 am
itmasterw 60042 - Wednesday, February 28, 2018 1:41 PMHi,I have been looking all over the web for this. I realize that SSIS does not, on its own handle SFTP. A number of sites suggest using WinSCP, but have not gives a full explanation of how to code for this. Maybe its me but they seem to leave things out. Like one site did that I need a FingerPrint variable, but did not explain what the was; I found that out on another site, but then that left out other things.
Does anyone know of a good site for this or maybe you might have a better way; I sure would be open to anything at this point.Thank you
Why can't you read this first ?
https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/
March 5, 2018 at 12:05 pm
itmasterw 60042 - Wednesday, February 28, 2018 1:41 PMHi,I have been looking all over the web for this. I realize that SSIS does not, on its own handle SFTP. A number of sites suggest using WinSCP, but have not gives a full explanation of how to code for this. Maybe its me but they seem to leave things out. Like one site did that I need a FingerPrint variable, but did not explain what the was; I found that out on another site, but then that left out other things.
Does anyone know of a good site for this or maybe you might have a better way; I sure would be open to anything at this point.Thank you
Did you check the documentation on WinSCP's site? You may want to read their documentation -
SFTP Task for SSIS
There are quite a few posts with examples on using WinSCP. The best thing to do is start trying to use it following those examples. Here is a basic example:
SFTP with SSIS Packages
Sue
March 6, 2018 at 10:33 am
I have been through these and they do not work. Part of it could be me, but if it is it because they are not given everything you need clearly. The best seems to be WiNSCP, but when I put it together, that is the Script file and run it from either a Bath file or the SSIS process task that they suggest it does not work.
For example, they tel you that you need an SHH key, but do not tell you where to get it. I figured it ou, but there are a umber of other things that I am guessing and apparently wrong.
need something that is more step by step and complete.
Tank you
March 6, 2018 at 2:34 pm
itmasterw 60042 - Tuesday, March 6, 2018 10:33 AMI have been through these and they do not work. Part of it could be me, but if it is it because they are not given everything you need clearly. The best seems to be WiNSCP, but when I put it together, that is the Script file and run it from either a Bath file or the SSIS process task that they suggest it does not work.
For example, they tel you that you need an SHH key, but do not tell you where to get it. I figured it ou, but there are a umber of other things that I am guessing and apparently wrong.
need something that is more step by step and complete.Tank you
What was wrong with the link I posted over on SQL Team? https://forums.sqlteam.com/t/problem-configuring-ftp-task/12627/6?u=jeffw8713
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 6, 2018 at 5:07 pm
Hi,
Well it is as I was saying this has I was saying only this is really involved . I mean I have not tried it but after reading through the whole thing it seems to be missing things.
First, it is only talking about down loading a file from the server, I want to upload. it has the Ssh and like I said that I found this but it is an example of how they leave things out.
o I have not gone through this one but I would hate to put this all together and find out that it does not work. Especially since this one is even more involved then the others that I wasted my time on. look if it were only to change the Get to put I would have no problem, but when I look at the script task I do not see what I would have to do to upload a
Thank you
March 6, 2018 at 5:49 pm
My problem, you say things didn't work, but you aren't showing us what you tried or the actual error messages you have been getting. We are all volunteers here trying to help you on our own time. Right now, we have no real idea what issues you have been experiencing.
Maybe someone here has already done the heavy lifting and has something they can share, I know I don't. If they do share it, you need to learn what was done and how it was done so that you could support it going forward.
A good way to learn is show us what you have done, where you are having difficulties and we can guide you to a solution that you can support.
March 7, 2018 at 7:41 am
You are right, and I appreciate all your help. I just answering why I did not try this last thing htat the person asked me. I have tried a few things and I have not gotten a lot of error messages. For example, I tried setting up a script with and Proceds task in SSIS for WINSCP as the directions state and when I run it this ssi what it gives:
"[Execute Process Task] Error: In Executing "C:\Program Files (x86)\WinSCP\WinSCP.exe" "-Script = C:\Users\ed.walsh\Desktop\uplaod.txt" at "C:\Program Files (x86)\WinSCP\", The process exit code was "1" while the expected was "0".". And am think you will agree that this really does tell us much. Other times, with other things that I try it gives nothing, just does not move the file. Of course, I am doing something wrong, but I believe that is due to a lack of information on the sites.
Now back to the post, where he asked me what was wrong with this I can see already I had a lot of questions in it that I, so can you blame me for not wanting to set yet another thing up that is not clear.
Unfortunately, I cannot show the script because that has company information in it (below I have it with fake information), but the nice thing about winSCP is that it generates the script for you based on you information. and I can tell you that is matches what the documentation shows. So I do not know.
But I did not mean to offend anyone I was just explaining whey I would not jump and use that solution.
My script:
# Connect to SFTP server using a password
open sftp://User:Pass@company.com/ -hostkey="ssh-dss 5555 XXXXXXXXXXXXXXXXXXXXXXXXXXXXX=" -rawsettings FSProtocol=2
# Upload file
put C:\Users\test\Desktop\testUpload.txt /home/user/
# Exit WinSCP
exit
March 7, 2018 at 8:48 am
itmasterw 60042 - Wednesday, March 7, 2018 7:41 AMYou are right, and I appreciate all your help. I just answering why I did not try this last thing htat the person asked me. I have tried a few things and I have not gotten a lot of error messages. For example, I tried setting up a script with and Proceds task in SSIS for WINSCP as the directions state and when I run it this ssi what it gives:
"[Execute Process Task] Error: In Executing "C:\Program Files (x86)\WinSCP\WinSCP.exe" "-Script = C:\Users\ed.walsh\Desktop\uplaod.txt" at "C:\Program Files (x86)\WinSCP\", The process exit code was "1" while the expected was "0".". And am think you will agree that this really does tell us much. Other times, with other things that I try it gives nothing, just does not move the file. Of course, I am doing something wrong, but I believe that is due to a lack of information on the sites.
Now back to the post, where he asked me what was wrong with this I can see already I had a lot of questions in it that I, so can you blame me for not wanting to set yet another thing up that is not clear.
Unfortunately, I cannot show the script because that has company information in it (below I have it with fake information), but the nice thing about winSCP is that it generates the script for you based on you information. and I can tell you that is matches what the documentation shows. So I do not know.
But I did not mean to offend anyone I was just explaining whey I would not jump and use that solution.My script:
Thank you
# Connect to SFTP server using a password
open sftp://User:Pass@company.com/ -hostkey="ssh-dss 5555 XXXXXXXXXXXXXXXXXXXXXXXXXXXXX=" -rawsettings FSProtocol=2
# Upload file
put C:\Users\test\Desktop\testUpload.txt /home/user/
# Exit WinSCP
exit
And did you look up what a return code of 1 from WinSCP meant? I just did, it meant an attempt to read past the end-of-file was made; or, there are no more directory entries to return.
Did you verify if the file(s) were set or received? I haven't used WinSCP in a long time, if ever, so I don't know what switches are available or logging capabilities but if you take the time to read the documentation that is available on the internet, I am pretty sure you can figure out what is going on, or at least be able to ask others more in depth questions to help get your process working.
March 7, 2018 at 11:09 am
Thanks for the replay, and yes if I take who knowns how long to read through he pages and pages for documentation I probably can put this all together do to their lack of explaining it fully to begin with. But I was originally asking if someone would have a place that I can go to find an accurate method and explanation (for any method ),so that I would not have to make it a major study. There should me a process that is not all this. For example, the standard FTP was extremely easy.
If not that is okay
Thanks
March 7, 2018 at 12:57 pm
itmasterw 60042 - Wednesday, March 7, 2018 11:09 AMThanks for the replay, and yes if I take who knowns how long to read through he pages and pages for documentation I probably can put this all together do to their lack of explaining it fully to begin with. But I was originally asking if someone would have a place that I can go to find an accurate method and explanation (for any method ),so that I would not have to make it a major study. There should me a process that is not all this. For example, the standard FTP was extremely easy.
If not that is okayThanks
If you want a built-in task, then I am sorry to say you are going to have to pay for it. Here are a couple:
https://pragmaticworks.com/Products/Task-Factory/Feature/Productivity/SSIS-SFTP-Secure-FTP-Task
If you are looking for something you are going to build, manage and maintain - then you need to work through the documentation from the utility you decide to use when you run into errors. What you should do is output the 'script' that is built so you can see what is being run - then try running that manually yourself to see why it is failing.
Like I stated before - I used the site I included to 'adapt' to my own solution. My solution requires:
Public Sub Main() 'Build the WinSCP "Script" in a string variable.
Dts.Variables("User::sftpCommand").Value = _
"option batch on" + vbCrLf _
+ "option confirm off" + vbCrLf _
+ "open " + Dts.Variables("User::sftpAddress").Value.ToString + vbCrLf _
+ "put " + Dts.Variables("User::OutputFilename").Value.ToString + " " + Dts.Variables("$Package::sftpDestinationFile").Value.ToString + "" + vbCrLf _
+ "exit" + vbCrLf
'MsgBox(Dts.Variables("User::sftpCommand").Value.ToString)
'Initialize the result of this command to an empty string.
Dts.Variables("User::sftpResults").Value = ""
Dts.TaskResult = ScriptResults.Success
End Sub
The next part is the Execute Process Task
The key to making this work is that the script component is generating the command line statements to be executed by WinSCP. The open statement is used to connect to the sftp server using the sftpAddress - the put statement puts the file (OutputFileName) - and then we exit...the options insure we have to correct settings enabled/disabled...
You can uncomment the MsgBox statement to see what is being generated - put that into a text file and call WinSCP.com directly using that text file. This should show you why the command is failing and then you can adjust as needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 8, 2018 at 7:49 am
itmasterw 60042 - Wednesday, March 7, 2018 11:09 AMThanks for the replay, and yes if I take who knowns how long to read through he pages and pages for documentation I probably can put this all together do to their lack of explaining it fully to begin with. But I was originally asking if someone would have a place that I can go to find an accurate method and explanation (for any method ),so that I would not have to make it a major study. There should me a process that is not all this. For example, the standard FTP was extremely easy.
If not that is okayThanks
That's a fair question, and you did get some links, but I appreciate your frustration that those links seem incomplete. You're right, authors don't often include every step or link to documentation. However, they're often writing with certain assumptions in mind. If you've never dealt with SSH, they aren't going to explain that, along with the subject they're tackling. Often you do need to backfill, which can be hard.
What I'd encourage you to do is if a link isn't helpful, like the MSSQLTips one, explain why? What item was confusing or difficult for you? Venting a bit is ok, but many here are volunteers and some of your posts appear to be asking them to do more work for you and produce a custom article or blog. Remember that we're helping, not doing work. The more specific you are with your frustrations or difficulties, and appreciating the help you get, the more people are willing to work with you.
March 8, 2018 at 7:55 am
Steve Jones - SSC Editor - Thursday, March 8, 2018 7:49 AMitmasterw 60042 - Wednesday, March 7, 2018 11:09 AMThanks for the replay, and yes if I take who knowns how long to read through he pages and pages for documentation I probably can put this all together do to their lack of explaining it fully to begin with. But I was originally asking if someone would have a place that I can go to find an accurate method and explanation (for any method ),so that I would not have to make it a major study. There should me a process that is not all this. For example, the standard FTP was extremely easy.
If not that is okayThanks
That's a fair question, and you did get some links, but I appreciate your frustration that those links seem incomplete. You're right, authors don't often include every step or link to documentation. However, they're often writing with certain assumptions in mind. If you've never dealt with SSH, they aren't going to explain that, along with the subject they're tackling. Often you do need to backfill, which can be hard.
What I'd encourage you to do is if a link isn't helpful, like the MSSQLTips one, explain why? What item was confusing or difficult for you? Venting a bit is ok, but many here are volunteers and some of your posts appear to be asking them to do more work for you and produce a custom article or blog. Remember that we're helping, not doing work. The more specific you are with your frustrations or difficulties, and appreciating the help you get, the more people are willing to work with you.
Operative words, work with you.
March 8, 2018 at 9:27 am
Just touched bases with a friend at a previous employer where we had to setup a SFTP process. Unfortunately that was back in 2007/2008 and that process has since been changed. With that much time since I last worked on an SFTP solution, I really can't be of much help. You really need to read as much documentation as you can, and if it doesn't make sense, ask specific questions to help you gain clarity.
March 8, 2018 at 11:22 am
Okay well I am going through the documentation now so we will see what happens, But thanks for taken the time to look at this fo rme.
Thank you
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply