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


Trying to get an SFTP process for SSIS


Trying to get an SFTP process for SSIS

Author
Message
itmasterw 60042
itmasterw 60042
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 477
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
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3674 Visits: 531
itmasterw 60042 - Wednesday, February 28, 2018 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

Why can't you read this first ?

https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/

Sue_H
Sue_H
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77777 Visits: 15570
itmasterw 60042 - Wednesday, February 28, 2018 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


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



itmasterw 60042
itmasterw 60042
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 477
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79554 Visits: 11497
itmasterw 60042 - Tuesday, March 6, 2018 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

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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

itmasterw 60042
itmasterw 60042
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 477
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401845 Visits: 43467
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
itmasterw 60042
itmasterw 60042
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 477
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:
[code language="WinSCP"]
# Connect to SFTP server using a password
open sftp://UserTongueass@company.com/ -hostkey="ssh-dss 5555 XXXXXXXXXXXXXXXXXXXXXXXXXXXXX=" -rawsettings FSProtocol=2
# Upload file
put C:\Users\test\Desktop\testUpload.txt /home/user/
# Exit WinSCP
exit
[/code]


Thank you
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401845 Visits: 43467
itmasterw 60042 - Wednesday, March 7, 2018 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:
[code language="WinSCP"]
# Connect to SFTP server using a password
open sftp://UserTongueass@company.com/ -hostkey="ssh-dss 5555 XXXXXXXXXXXXXXXXXXXXXXXXXXXXX=" -rawsettings FSProtocol=2
# Upload file
put C:\Users\test\Desktop\testUpload.txt /home/user/
# Exit WinSCP
exit
[/code]


Thank you


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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
itmasterw 60042
itmasterw 60042
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 477
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
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