use FTP task to connect FTP/SSL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

use FTP task to connect FTP/SSL server

Author

Topic

tinysnail
Starting Member

3 Posts

Posted - 2007-11-01 : 10:31:02

Hi,

I need to connect to a FTP/SSL server to grab a file in my SSIS package. But I cannot find any option in FTP task to specify the connection type. Can some one help me out?

Thank in advance.

-tinysnail

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-01 : 23:02:34

Why don't just run dos command?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-02 : 11:19:54

I have to use a stored proc that I found from here to do the job.
Then in SSIS, add a exec sql step to call it.

You have to enable the cmd shell on the sql server surface management to use this.

Other conditions include: the file name is known, and has created a folder on C:\temp for the script file.

Create procedure ps_ftp_GetFile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128),
@SourcePath varchar(128),
@SourceFile varchar(128),
@workdir varchar(128)

AS

/*
exec ps_ftp_GetFile
@FTPServer = 'ftp.site.com' ,
@FTPUser = 'FTPuser' ,
@FTPPWD = 'pwd' ,
@FTPPath = '/xyz/' ,
@FTPFileName = 'impFile.txt' ,
@SourcePath = '\\myUNC\' ,
@SourceFile = 'impSFile.txt' ,
@workdir = 'c:\temp\'

*/

declare @cmd varchar(1000)
declare @workfilename varchar(128)

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'get ' + @FTPPath + @FTPFileName + ' ' + @SourcePath + @SourceFile
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + @workdir + @workfilename

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources