DTS File Transfer Protocol Task

Question

DTS File Transfer Protocol Task

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

Phil, sorry for the slow responce, where abouts shout the bat file be, I thought it would be in the same directory as the file, but none is there?

Does the SP delete the file after it is executed?

Cyrus Bharda

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

The batch file should reside in the same directory as the file you're pushing to the FTP server. If it's not there, then there is a problem creating the file. Before the line that reads "EXEC master.dbo.xp_cmdshell @cmd", try putting EXEC (@sql). When you run the procedure in query analyzer you should see what will go into the batch file.

If you execute the procedure as,

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

Then the batch file should contain the following,

username

password

cd /<destination directory path>

put blah.csv

bye

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 3

Sorry for the troubles . I forgot that I was only able to send to the root - I had just learned to live with it. However, with Phil's suggestions we will all have an improved product.

Use:

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

and make sure that @ftp_to_server does not include a sub directory, also as Phil indicated use local path. Note that ftp files and folders can be case sensitive.

Alternatively, include a parameter for the sub directory by adding 2 lines:

In the declarations:

@ftp_subdir varchar(255),

and

In Create table ##temp_ftp_bat:

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

(replace '&' with plus sign - I can't get plus sign to show)

Then call by using for example:

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

Substitute '.' for the root directory instead of 'exports'.

Cyrus, regarding the batch file, ftp can use a text file to store a series of commands. This procedure simply combines your parameters from the procedure with the ftp commands into a temp table that is then bcp'd out to create the text file with commands and parameters that the ftp program inherent in windows uses to send your file. Like me, you may have thought that the ftp -s parameter means "send", when actually it appears to stand for "script". The main pupose of the temp tables appears to be for error handling.

Hopefully, it will now work smoothly for you.

Bill

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

If you want to see the batch file, comment out the following line near the end:

set @cmd = 'del '+@batch_ftp

It cleans up after itself by deleting the batch file at the end.

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

Bill/Phill,

You guys are the best, I added the line that Bill suggests:

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

And it all works sweet as, I cannot thank you two enough!

Thank you for the help and support with my problem!!

Cyrus Bharda