In message <6168E0A5-8A20-4319-81C1-7AD2E7C4DB44 (AT) microsoft (DOT) com>, Lenny
<lp (AT) TNSIntersearch (DOT) com> writes
You can do this, but you will need to use an ActiveX Script Task to
manipulate the FTP task-

Dim oPkg

' Get current package instance
Set oPkg = DTSGlobalVariables.Parent

' Get existing FTP task by name
' Note, name may need to be changed to match your FTP task
Set oFTPTask = oPkg.Tasks("DTSTask_DTSFTPTask_1").CustomTask

' Set properties of FTP task
oFTPTask.SourceFilename = "'test.zip';'filedir"';''"

' Clean Up
Set oFTPTask = Nothing
Set oPkg = Nothing

You will need to change "test.zip" toy you filename, derived using the
VBScript date functions. Note the 3 part format of SourceFilename, as
documented in Books Online.

An example of deriving filenames from dates cane be found here-

How can I change the filename for a text file connection?
(http://localhost/SQLDTSNet/default.aspx?200)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

I don't like the FTP task in DTS, and I use a work around that consists of
dynamically creating an ftp file and then use ftp in the commandshell to
execute that file (courtesy of Philip Livingstone):
"
Thanks guys but we decided to use a little DOS trickery to
workaround like this:

exec master..xp_cmdshell 'Echo Open 1.2.3.4 >C:\ftp.txt &&
echo user >>C:\ftp.txt && echo dev >>C:\ftp.txt && echo
pdev >>C:\ftp.txt && echo get
LUX1MCH.LUX "\\localserver\share\dir\file.ext"

basically we tell DOS (through xp_cmdshell) to create the
script file, write in the IP address and user and password
and everything else to execute our data transfer using
FTP, then delete the script file - all in one line of
code...
"


--
Jacco Schalkwijk
SQL Server MVP


"Lenny" <lp (AT) TNSIntersearch (DOT) com> wrote

date e.g. mmddyyyyFilename.csv. Is FTP task flexible enough to handle this
kind of scenario? If yes, how do you set it up?

Thank you all for your comments

Could you tell me any specific issues you had with DTS FTP. I need to make a decision soon if I should use DTS with its FTP task for daily data download process, or develop my own custom solution.
That ActiveX script that Darren posted worked for me, thanks Darren! However if I am going to run into any problems later on, I would like to know about them now. Thank

The functionality of the DTS FTP task is quite limited:
1) You can only upload or download files, you can't delete them.
2) It only does binary transfers, no text transfers. (That means you have to
convert Unix LF's to Windows CR/LF's separately).
3) You can only download one file per FTP task.
4) If your filename includes a datetimestamp, you have to change the
properties of the task.

I admit that most of my complaints date back from a project in SQL Server 7
about 5 years ago when we had to download files from an Oracle box on UNIX
(no Samba in those days, and they didn't allow us access to the Oracle
database directly). Having the Dynamic Properties task in SQL Server 2000
has addressed point 4, and in some way point 3. But I found that using an
ftp script file was a lot more convenient than the DTS FTP task, so I stuck
to that.

--
Jacco Schalkwijk
SQL Server MVP


"Lenny" <anonymous (AT) discussions (DOT) microsoft.com> wrote

download process, or develop my own custom solution.
know about them now. Thanks