April 15, 2008 at 6:15 am
helo,
i have created an excel file(using Integrated service in SQL) which is populated with data in a table in my database when debugging...i have converted the files to .csv and now i have to send the file using FTP Task....i used the FTP task editor and configure for 'file transfer'...RemotePath and localPath must be specified..when i click onthe ellipsis at RemotePath, i select the folder name test...
Now i debug the project and i get error..the FTP Task is red...what can me error be? is there something i must add to the design to specify the detination FTP? plz help..urgent
April 16, 2008 at 9:27 am
Create procedure [dbo].[ftp_UploadFile]
@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 s_ftp_PutFile
@FTPServer = 'myftpsite' ,
@FTPUser = 'username' ,
@FTPPWD = 'password' ,
@FTPPath = '/dir1/' ,
@FTPFileName = 'test2.txt' ,
@SourcePath = 'c:\vss\mywebsite\' ,
@SourceFile = 'MyFileName.html' ,
@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 ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'ftp -s:' + @workdir + @workfilename
create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
Place this code in your job and schedule it.
exec s_ftp_PutFile
@FTPServer = 'myftpsite' ,
@FTPUser = 'username' ,
@FTPPWD = 'password' ,
@FTPPath = '/dir1/' ,
@FTPFileName = 'test2.txt' ,
@SourcePath = 'c:\vss\mywebsite\' ,
@SourceFile = 'MyFileName.html' ,
@workdir = 'c:\temp\'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply