Link to home
Start Free TrialLog in
Avatar of ccptechs
ccptechs

asked on

Dynamic Filename In SQL Server 2000 DTS Package FTP Task

I am trying to write a SQL Server 2000 DTS package that will pull down a file using SQL Server's "FTP TASK". The catch is... the file that I am trying to pull down has a filename that is based on the date that the file was created. For example: the filename is '03012007.ZIP' on March 1st, 2007. On March 2nd, 2007, the filename will be: '03022007.ZIP', etc... I will need to write a VB Script that will dynamically create the name each day.

Question is:
What would I do to my DTS 'FTP Task' so that I can pass it the dynamic filename each day?
*I ask that you please be as specific as possible in your responses since I am so new with DTS.
ASKER CERTIFIED SOLUTION
Avatar of navalarya1982
navalarya1982

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ccptechs
ccptechs

ASKER

Thank you navalarya1982! That worked great...
When I execute the steps separately this works, but when I execute the package I get "Invalid string format for source file name".  I'm SO close, please help.  Thank you.
select 'dhl_vacdintv' + convert(varchar, getdate()-1,112) + '.xls'

Open in new window

Avatar of Anthony Perkins
>>I'm SO close, please help. <<
That is because you are missing step 3.  Workflow needs to be added/corrected.
I thought I did?  Please advise.  I appreciate this
temp.bmp
That seems correct.  Intead of On Complete I would use On Success, but that should not make any difference.  Truth be told I do not use the Dynamic Properties Task but rather an ActiveX Script Task with VBScript code instead of T-SQL.  But again, that should not make any difference.  The only thing I can think of is to change this:
select 'dhl_vacdintv' + convert(varchar, getdate()-1,112) + '.xls'

To the more appropriate:
select 'dhl_vacdintv' + convert(varchar(8),  getdate() - 1, 112) + '.xls'

But I am grasping at straws.