DTS File Transfer Protocol Task

Question

DTS File Transfer Protocol Task

Cyrus.Au

Mr or Mrs. 500

Points: 505
More actions
September 24, 2003 at 10:11 pm

#106300

Hello,
I am trying to put the ftp task on the end of my DTS package, and transfer a file from the local hard drive to a remote ftp. But by the looks of it, you can only set the destination as a local hard drive? Is there any way of setting the destination as an external ftp site?
Thanks in advance!
Cyrus Bharda

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

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

Bill Nye Hall of Fame Points: 3248 More actions · Answer 1

This will do it: (Thanks again to EDK89 - This was a big help to me-the only change I recall making was to add the @servername since I was using a named instance).

 /* From sqlservercentral.com

edk89 

Starting Member 

USA 

2 Posts Posted - 12/09/2002 :  3:33:51 PM       

--------------------------------------------------------------------------------

Here is a stored procedure to ftp files using SQL Server stored procedure:

*/

USE Tempdb

-- creating the store procedure for FTP

IF EXISTS (SELECT name 

   FROM   sysobjects 

   WHERE  name = N'up_FTPPushFile' 

   AND   type = 'P')

    DROP PROCEDURE up_FTPPushFile

GO



Create proc up_FTPPushFile

@file_to_push varchar(355), 

@ftp_to_server varchar(355),

@ftp_login varchar(255), 

@ftp_pwd varchar(255) 

as

Set Nocount On

--STEP 0

--Ensure we can find the file we want to send.

Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)

BEGIN

Drop table #FileExists

RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)

RETURN 1

END 

--STEP 1

--Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push

--batch file will hold 4 records:

--1) login

--2) password

--3) ftp command and file to push

--4) exit command

declare @sql varchar(455), @cmd varchar(355), @batch_ftp varchar(355), @ret int

declare @servername varchar(50)

set @servername = @@servername

set @sql = '"SELECT ftp_batch FROM ##temp_ftp_bat WHERE file_to_push = '''+ @file_to_push+'''"'

set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'

set @cmd = 'BCP '+ @sql +' queryout '+ @batch_ftp +' /S '+ @servername + ' /T /c' 

Create table ##temp_ftp_bat(ftp_batch varchar(355), file_to_push varchar(355))

Insert into ##temp_ftp_bat values (@ftp_login, @file_to_push)

Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)

Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)

Insert into ##temp_ftp_bat values ('bye', @file_to_push)

EXEC master.dbo.xp_cmdshell @cmd

Drop table ##temp_ftp_bat

--STEP 2

--Ensure we can find the batch file we just created.

Delete #FileExists

Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)

BEGIN

Drop table #FileExists

RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)

RETURN 1

END 

Drop table #FileExists

--STEP 3

--Execute newly created .bat file, save results of execution

Create table #temp_ftp_results (ftp_output varchar(355))

set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server

Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd

IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))

BEGIN

Drop table #temp_ftp_results

RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)

RETURN 1

END 

Drop table #temp_ftp_results

--STEP 3

--delete batch file

set @cmd = 'del '+@batch_ftp

EXEC master.dbo.xp_cmdshell @cmd



go

philcart SSC-Forever Points: 47795 More actions · Answer 2

As you're already utilising DTS, why not use an ActiveXscript task to perform the transfer? Or, even do as the above post from BillNye101 does, create a short batch file on the fly then have an ExecuteProcess task to run the batch file.

I'd prefer to use the first method because you can do everything in the one step.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Cyrus.Au Mr or Mrs. 500 Points: 505 More actions · Answer 3

Wow, great thanks for the help, only problem is that I am a noob and have no idea exactly what I am looking at for that script above, still trying to make sence of it 🙂 but at least now I have something to try to get working 🙂

I just thought the ftp task should be able to send and receive from a remote ftp, not just recieve, but then again, that would make it usefull 🙂

Thanks a lot for the help muchly appreciated!!

Cyrus Bharda

Bill Nye Hall of Fame Points: 3248 More actions · Answer 4

Perhaps this will help a little more.

Just run the code above to create the ftp proc (heck, I don't claim to understand all the details anymore than I know how the electrons are running through my cpu, but I know it works ). Then to ftp use:

exec up_FTPPushfile 'C:\ftpfile.ext', 'FTP.abc.com', 'username', 'password'

I agree; you'd think the FTP task would export as well as import - maybe in the next version.

Phill, not that I want to change something that is working smoothly for me, but I am curious as to how your ActiveX works. Is it small enough that you could post an example?

Thanks

Bill

philcart SSC-Forever Points: 47795 More actions · Answer 5

Bill

The ActiveX script is similar to the T-SQL solution you provided. It builds a list of ftp commands in a file and executes the ftp program with the -s option.

I'm working on setting up a stand-alone package that you can pass parameters to. Sort of like a custom task, but without the hassles of registering an extra component on the server. The infrastructure support guys get a bit funny about putting any extra dll's on the server

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Cyrus.Au Mr or Mrs. 500 Points: 505 More actions · Answer 6

Bill,

Well ran the script and it executed fine, so I then tried this:

exec up_FTPPushfile '\\serevr\directory\blah.csv', 'ftp.com.au', 'username', 'password'

and got this error:

Server: Msg 50000, Level 16, State 1, Procedure up_FTPPushFile, Line 45

Unable to create FTP batch file \\serevr\directory\blah.bat. FTP process aborted.

Which I dont really understand? What's going on, why is it trying to make a bat file?

Thanks again for your help, muchly appreciated!

Cyrus Bharda

Cyrus.Au Mr or Mrs. 500 Points: 505 More actions · Answer 7

Oh and might there be a way of specifying a directory to upload to on the ftp?

Thanks,

Cyrus Bharda

philcart SSC-Forever Points: 47795 More actions · Answer 8

Cyrus

The error message is referring to the section of the procedure where it checks for the ftp batch file that it creates. Try using a local path & directory instead of the UNC path. eg: C:\Temp\...

Also, you can specify the directory on the FTP server. You'll need to add in a line to change to the required directory. Before the line that reads,

quote:
Insert into ##temp_ftp_bat values ('put '...

Insert a new line add put in,

quote:
Insert into ##temp_ftp_bat values ('cwd <insert your directory>'

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Cyrus.Au Mr or Mrs. 500 Points: 505 More actions · Answer 9

Phil,

Yeah it does help if I have shared the bloody directory, <bonks self>.

Well, it worked great, but when I added in the line to specify the directory like so:

Insert into ##temp_ftp_bat values ('cwd blah')

I got this:

Server: Msg 213, Level 16, State 4, Procedure up_FTPPushFile, Line 34

Insert Error: Column name or number of supplied values does not match table definition.

So do I need to add something to a table somewhere?

Thanks a lot Bill and Phill for helping me out!!

Cyrus Bharda

philcart SSC-Forever Points: 47795 More actions · Answer 10

Sorry, didn't see that there was more than one column in the temp table.

Your CWD line should read,

quote:
Insert into ##temp_ftp_bat values ('cwd <insert your directory>', @file_to_push)

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Cyrus.Au Mr or Mrs. 500 Points: 505 More actions · Answer 11

Phill,

OK well it's executing fine, but still not changing the directory. This is what I tried:

Insert into ##temp_ftp_bat values ('cwd blah', @file_to_push)

Insert into ##temp_ftp_bat values ('cwd /blah', @file_to_push)

Insert into ##temp_ftp_bat values ('cwd \blah', @file_to_push)

Insert into ##temp_ftp_bat values ('cd exports', @file_to_push)

Insert into ##temp_ftp_bat values ('cd \exports', @file_to_push)

Insert into ##temp_ftp_bat values ('cd /exports', @file_to_push)

None worked, the file still got uploaded, but only to the root directory. It must be just the wrong command or the command is not working because the file gets uploaded, just not to the right directory.

Thanks yet again for the speedy responces!!

Cyrus Bharda

Cyrus.Au Mr or Mrs. 500 Points: 505 More actions · Answer 12

Oh and before I go sticking my foot into my mouth again, both directories that I tried (blah & exports) both exist and have 750 permissions on them so this time I dont think it's a permissons problem, I hope 🙂

Cyrus Bharda

philcart SSC-Forever Points: 47795 More actions · Answer 13

Can you post the contents of the batch file?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Bill Nye Hall of Fame Points: 3248 More actions · Answer 14

Phil, thanks for the info on the ActiveX. Now that I've spent a little more time looking at ftp commands, I understand better.

Here is what the batch file looks like:

user

pwd

put c:\filename

bye

Bill