January 22, 2016 at 12:24 pm
Hi all,
My end objective is to load a file that is on FTP server into SQL Server using T-SQL commands.
Situation:
- SQL Server is in data center
- Using a program called WebDrive, I mounted FTP drive as W drive (http://southrivertech.com/products/webdrive/)
- When I execute the bulk insert statement on the Server (RDP to login to server -> SQL Server Management Studio -> execute bulk insert script like something below) everything works fine
- Sample script:BULK INSERT CSVTest FROM 'W:\csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' )
- When I login to SQL Server via my SQL Server Management Studio on my laptop and try to execute the above script, it returns an error: Cannot bulk load because the file "W:\csvtest.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).
Any suggestions? Thanks for your help.
January 22, 2016 at 1:38 pm
murthyvs (1/22/2016)
Hi all,My end objective is to load a file that is on FTP server into SQL Server using T-SQL commands.
Situation:
- SQL Server is in data center
- Using a program called WebDrive, I mounted FTP drive as W drive (http://southrivertech.com/products/webdrive/)
- When I execute the bulk insert statement on the Server (RDP to login to server -> SQL Server Management Studio -> execute bulk insert script like something below) everything works fine
- Sample script:BULK INSERT CSVTest FROM 'W:\csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' )
- When I login to SQL Server via my SQL Server Management Studio on my laptop and try to execute the above script, it returns an error: Cannot bulk load because the file "W:\csvtest.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).
Any suggestions? Thanks for your help.
What is the "W:" drive mapped to?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2016 at 1:59 pm
Hi Jeff - "W" is a FTP folder mounted as drive using a program called Webdrive. Thanks
January 22, 2016 at 2:13 pm
murthyvs (1/22/2016)
Hi Jeff - "W" is a FTP folder mounted as drive using a program called Webdrive. Thanks
SQL Server can't see that. You need to use the full UNC to that and the Login for the SQL Server Service needs to see it. If you're using BULK INSERT, even that won't help unless you set up a trusted relationship between the machines, which is why I typically use xp_CmdShell and BCP so that I don't have to commit such a sin.
Ok, now duck because the zealot's that incorrectly state that any use of xp_CmdShell is a security risk are about to fly in. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2016 at 5:20 pm
Jeff Moden (1/22/2016)
murthyvs (1/22/2016)
Hi Jeff - "W" is a FTP folder mounted as drive using a program called Webdrive. ThanksSQL Server can't see that. You need to use the full UNC to that and the Login for the SQL Server Service needs to see it. If you're using BULK INSERT, even that won't help unless you set up a trusted relationship between the machines, which is why I typically use xp_CmdShell and BCP so that I don't have to commit such a sin.
Ok, now duck because the zealot's that incorrectly state that any use of xp_CmdShell is a security risk are about to fly in. 😉
Not yet they aren't. 😉
I've never heard of WebDrive being installed on the SQL Server before. I would think that extra layer is going to cause you problems later in life. There are several people at the company I work for who use WebDrive on their workstations. Over time, the WebDrive errors break things that run overnight. It can be recovered from using a batch file when it fails, but it's a point of failure you just don't need to live with. My recommendation is to uninstall it and pursue a SQL-only approach. Unless you're using SFTP, it's actually pretty simple.
Using xp_cmdshell, you can shell out to execute an FTP script to pull the file down to SQL Server's file system. If the downloaded filename is already constant, then skip the rest of this paragraph. If the downloaded filename is variable (determined by date or something similar) you can shell out again to copy the downloaded file to a constant filename. This gives you the advantage of maintaining the historical files you've downloaded. If you don't want to save then, simply rename the downloaded file to a constant name. The reason for the rename is that in your BULK INSERT statement, you need to provide a constant for the filename.
After you have the file downloaded from the FTP server to SQL Server's file system, you can fire your BULK INSERT statement to import the file. I have a system in place that's run successfully for just over 6 years using this exact approach.
Edit: Here's an excerpt of a script that illustrates how to do it. This isn't my actual script - it's sanitized, so please forgive me if I missed anything and introduced an error.
SET @strPath = 'E:\Download';
SET @strDataFile = 'ImportData.txt';
SET @strCmd = 'echo ftp_user_name>' + @strPath + '\script.ftp&' +
'echo ftp_password>>' + @strPath + '\script.ftp&' +
'echo bin>>' + @strPath + '\script.ftp & ' +
'echo prompt n>>' + @strPath + '\script.ftp & ' +
'echo lcd ' + @strPath + '>>' + @strPath + '\script.ftp & ' +
'echo cd /Inbox >>' + @strPath + '\script.ftp & ' +
'echo get ' + @strDataFile + '>> ' + @strPath + '\script.ftp & ' +
'echo quit>>' + @strPath + '\script.ftp'
EXECUTE master..xp_cmdshell @strCmd;
--shell out to the ftp script we just created
SET @strCmd = 'ftp.exe -v -s:' + @strPath + '\' + 'script.ftp some_ftp_server.com';
EXECUTE master..xp_cmdshell @strCmd;
You can see where you need to replace some_ftp_server, ftp_user_name and ftp_password with your values. To debug your FTP script, you can comment out the EXECUTE statements and use PRINT @strCmd to print them out. When you get the commands done, get rid of the print statements and uncomment the EXECUTEs.
I hope this helps get you closer to where you need to be. Post back if you need a hand, but don't post your actual FTP connection info.
January 26, 2016 at 10:55 am
@jeff - Thanks for your advice.
@Ed - Thanks for your code snippet. It works. Here is what I had done.
declare @strPath varchar(2000);
declare @strDataFile varchar(2000);
declare @strCmd varchar(2000);
declare @scriptName varchar(100);
SET @strPath = 'D:\TestDownload';
SET @strDataFile = '\TestFileDir\TestFile.txt';
SET @scriptName = 'TestFile'
-- comment this portion after *.ftp file is created
SET @strCmd = 'echo ftp_user_name>' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo ftp_user_password>>' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo bin>>' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo prompt n>>' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo lcd ' + @strPath + '>>' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo cd /Inbox >>' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo get ' + @strDataFile + '>> ' + @strPath + '\'+ @scriptName +'.ftp&' +
'echo quit>>' + @strPath + '\'+ @scriptName +'.ftp'
-- print (@strCmd)
EXECUTE master..xp_cmdshell @strCmd;
-- comment this portion after *.ftp file is created
--shell out to the ftp script we just created
SET @strCmd = 'ftp.exe -v -s:' + @strPath + '\'+ @scriptName +'.ftp ' +' http://ftp.ftp-client.com';
--print (@strCmd)
EXECUTE master..xp_cmdshell @strCmd;
Is it possible to delete the file with the command once my bulk insert is done? If so, can you please share what that script would look like?
Thanks
January 26, 2016 at 12:11 pm
Excellent. I'm glad to hear you have it working
It's absolutely possible to delete your script.ftp file once you're done with it. In fact, you can do it before you fire the BULK INSERT. After all, you have the file on the server, so you don't need the script.ftp file any longer.
SET @strCmd = 'if exist ' + @strPath + '\script.ftp del ' + @strPath + '\script.ftp';
EXECUTE master..xp_cmdshell @strCmd;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply