connect to Sftp using putty ssis

Question

connect to Sftp using putty ssis

afgone

SSCommitted

Points: 1551
More actions
April 10, 2008 at 5:48 am

#121386

Hi,
I want to connect to SFTP server and download files using SSIS. I know ssis doesn't support sftp connection. I was told i can use putty to do the job(I dont want third party components).
Is it possible you guyz show me how to do this, using a tutorial or how to create the batch file and call it in ssis package.
thanks.

Viewing 15 posts - 1 through 15 (of 40 total)

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

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

Look at using the Execute Process Task on the Control Flow canvas.

First, you should get your SFTP command working outside of SSIS the way you want, then transfer the commands to the task.

One thing you might run into with PuTTY is that it may want to save a 'fingerprint' the first time you connect with another server. That 'fingerprint' is generally stored in the current users Windows profile. The reason that becomes a problem is when the SSIS package is launched outside of BIDs it's typically done so under different credentials, which probably don't contain that 'fingerprint'.

afgone SSCommitted Points: 1551 More actions · Answer 2

hi,

thanks for your suggestion.

Do you have a sample of the connection in a batch file or something... as i seem to have problem connecting. it says host not found.

thanks

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

Use an execute SQL task to build an SFTP command file with xp_cmdshell. Here is an example:

DECLARE @MailID varchar(8)

DECLARE @FileName varchar(100)

DECLARE @Cmd1 varchar(1000)

DECLARE @Cmd2 varchar(1000)

DECLARE @Cmd3 varchar(1000)

SELECT @Cmd1 = 'echo lcd C:\MYSFTP_DIR>C:\MYSCRIPT_DIR\PSFTP_CMDFILE.txt'

SELECT @Cmd3 = 'echo exit>>C:\MYSCRIPT_DIR\PSFTP_CMDFILE.txt'

EXEC master.dbo.xp_cmdshell @Cmd1

DECLARE iSFTPCursor CURSOR FOR SELECT MAIL_ID FROM dbo.SOMETABLE WHERE IsActive = 1

OPEN iSFTPCursor

FETCH NEXT FROM iSFTPCursor INTO @MailID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FileName = 'mget /' + @MailID + '_S_PRC_' + LEFT(REPLACE(CONVERT(CHAR(8), GetDate(), 10), '-', SPACE(0)),4) + '0.*'

SET @Cmd2 = 'echo ' + @FileName + '>>C:\MYSCRIPT_DIR\PSFTP_CMDFILE.txt'

EXEC master.dbo.xp_cmdshell @Cmd2

FETCH NEXT FROM iSFTPCursor INTO @MailID

END

EXEC master.dbo.xp_cmdshell @Cmd3

CLOSE iSFTPCursor

DEALLOCATE iSFTPCursor

You can then use an execute process task for the PuTTy SFTP binary - i.e.

sftp.somesdomain.com -l someuser -pw somepassword -b PSFTP_CMDFILE.txt

I've used this particular method several times w/ great success. Good luck 🙂

Tommy

Follow @sqlscribe

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 4

Nope, no examples. But if you post what you've done I'll look at it.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 5

See examples above -

BTW - as Todd suggested earlier, create a proxy account for this package so that the fingerprint can be stored under the proxy account's credentials (i.e. you will need to connect to the remote SFTP server under the proxy account's credentials on the SSIS server the first time to store the fingerprint).

Good luck 🙂

Tommy

Follow @sqlscribe

afgone SSCommitted Points: 1551 More actions · Answer 6

thanks for the reply everyone... so far i managed to create a batch file that connects to server and downloads the files. now i want to move the file in the server from the current directory to another directory( in the same server)... is this possible? is there a command for it...

@tommy your method is too hard but i will give it a try to see which one works better.

thanks

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 7

You might be looking for the mv command.

The documentation on the PuTTY website has many examples: http://the.earth.li/~sgtatham/putty/0.60/htmldoc/Chapter6.html#psftp

afgone SSCommitted Points: 1551 More actions · Answer 8

Hi,

I have tried it but for some reason it doesnt work it says remote to remote not supported

also how would i echo the date and time , i have said echo %Date% %time% >>"C:\Program Files\test.txt" but nothing gets displayed in the folder

thanks

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 9

If the SFTP server doesn't support that command then you're left with getting the file, putting the file back in the new location, then removing the file from the original.

I don't believe date and time are environment variables so using percent signs won't work. You'd have to do something like this.

date /t &&amp time /t >&gt test.txt

afgone SSCommitted Points: 1551 More actions · Answer 10

thanks but date time still does not work.

also how would i erase the .csv file in server. i managed to download them then upload them but cant erase...

i have :

pscp -2 -l username -pw password -i "C:\PUTTY\Keys\tl.ppk.ppk" rm username@host:folder/*.csv

thanks

afgone SSCommitted Points: 1551 More actions · Answer 11

afgone

SSCommitted

Points: 1551

April 11, 2008 at 2:10 am

#801333

Anything on this please ^^^

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 12

afgone (4/11/2008)
Anything on this please ^^^

Does Secure Copy support removing files? You might be confusing Secure Copy(pscp) with the Secure FTP client (psftp).

As far as the date and time just add another redirection, or put it on seperate lines.

date /t >> myfile.txt && time /t >> myfile.txt

or

date /t >> myfile.txt

time /t >> myfile.txt

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 13

Use a script task in SSIS to delete the file (assumes string variables varFileDir, varFileName)

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

'thomas_bollhofer@symantec.com

Try

File.Delete(Dts.Variables("varFileDir").Value.ToString & "\" & Dts.Variables("varFileName").Value.ToString)

Dts.Events.FireInformation(0, "", "File Deleted Succesfully", "", 0, True)

Catch ex As Exception

Dts.Events.FireError(0, "", "File Does Not Exist", "", 0)

End Try

'For Debugging Purposes

'System.Windows.Forms.MessageBox.Show(Delete(Dts.Variables("varFileDir").Value.ToString & "\" & Dts.Variables("varFileName").Value.ToString))

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Tommy

Follow @sqlscribe

Wayne West SSC-Insane Points: 22586 More actions · Answer 14

Your best bet for getting the files into a specific directory is to either move them through a CMD file (which is how I invoke PSFTP), or to download them directly into said directory, which is how I do it. The mv command, I'm fairly sure, only deals with files on the remote host.

My problem is dealing with the file remaining on the host system. I think I'm going to have to determine the file name in DTS then create a script to reconnect to the remote host and rename the files up there, apparently the mv and rename commands don't accept wildcards. I have to use mget *.txt to get my file down since the file name changes every day.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]