FTP Push in DTS

Question

FTP Push in DTS

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
April 14, 2004 at 7:46 am

#359421

I need to push files using FTP at the end of a DTS workflow. I can't get access to xp_cmdshell, and I'm not particularly fond of the solutions that code the FTP commands (including userid and password) into text files and then invoke the FTP command, requiring BAT files to run.
There used to be a good FTP Custom Task floating around somewhere that enabled you to set up an FTP Push task at the end of your workflow. I lost track of it through a series of upgrades. Now I can't find anything that works right. The one on SQLDTS.com doesn't compile correctly and even when I fix the compile errors it doesn't work properly in DTS after being registered as a Custom Task.
Does anyone have a line on an FTP custom task that can be used to push files from a DTS package? Any input would be much appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

There is a real good article here http://www.sqlteam.com/item.asp?ItemID=6002

I used this as a basis, made a couple of changes and enhancements (error checking and the like) and use it now with stored procs to do 90% of my ftp stuff.

Shamless self promotion - read my blog http://sirsql.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

The link you posted points to code that's executed in T-SQL which would require using a SQL Task in DTS with all the code buried in it. Plus the example only shows a GET operation with no doc on how PUT works.

SQL2K already has a DTS Task object that handles inbound FTP (GET) without requiring you to register DLL's and write code. I'm looking for the complement, an outbound FTP (PUT) task. I know they're out there, I just can't seem to dig out one that works well.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

A small futz around with the dll will allow you to do puts as well. That and the creation of a new proc.

Shamless self promotion - read my blog http://sirsql.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

I opened the CLS file in Visual Studio and updated the functions and API calls based on the WinInet doc. Then I went looking for the option to build a DLL and there was none.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

Are you running VS.Net? I had the same problem, you'll need to compile in VB6.

Shamless self promotion - read my blog http://sirsql.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 6

I was in VB6. Since there was no project file I had to set the Project Properties to ActiveX DLL, No Startup, Set the Project Name, then it let me compile it. I don't know if it will work, but it's a start...

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

I have the compiled DLL, but can't find the pre-compilation source (lost in the job changeover a few months back). I can email you the dll and the procs if it would be of help.

Shamless self promotion - read my blog http://sirsql.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 8

I had several versions of this I picked up from various sources and I was able to get one of them working. I need to look and see if there's a way to support multiple file transfer (i.e. *.txt) in one operation, but that's not a show stopper.

Thanks for the help.

Peter P SSCrazy Points: 2777 More actions · Answer 9

Try vbSendMail (freeware). Its always worked great for me.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 10

A new and very complete FTP Custom Task was posted on http://www.sqldts.com. I downloaded and implemented it and it's great.