Using xp_cmdshell to Import text file from FTP site

Last Post 02 Oct 2008 12:34 PM by Amber_Robertsona. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Amber_Robertsona
New Member
New Member

--
02 Oct 2008 02:36 AM
Dear All, I adapted the stored procedcure I found on the internet which I thought would log onto FTP site and import the data into a temporary table. However it keeps failing with the error message "Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 1
Insert Error: Column name or number of supplied values does not match table definition." Can anybody suggest why this keeps failing? Sorry I haven't had much experience using xp_cmdshell. The code I am using:

exec s_ftp_GetFile
@FTPServer = 'myftpserver' ,
@FTPUser = 'myaccount' ,
@FTPPWD = 'mypassword' ,
@FTPPath = '' ,
@FTPFileName = 'bodenuk-ratings.txt' ,
@SourcePath = 'feeds\' ,
@SourceFile = 'bodenuk-ratings.txt' ,
@workdir = 'c:\temp\'



ALTER PROCEDURE s_ftp_GetFile
/* Tsql from internet (Nigel Rivett, www.nigelrivett.net/FTP/s_ftp_GetFile.html)
Retrieve file from specified FTP site. Please note xp_cmdshell needs to be enabled before this sp is executed.*/

@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

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

CREATE TABLE #a (ProductID Varchar(10),AverageRating Int,NumReviews Int)
INSERT #a
EXEC master..xp_cmdshell @cmd

SELECT * FROM #a



GO

Any suggestions much appreciated.

Kind Regards
Amber






Amber_Robertsona
New Member
New Member

--
02 Oct 2008 12:34 PM
Hi, thank you for getting back to me. I have now recreated the solution using SSIS instead.


Kind Regards
Amber


Acceptable Use Policy
---