Can you work out logically what the file name will be?

If you can then it's as easy as modifying this article

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

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


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

DTS. The problem I am having now is that the naming schema of raw data file
is going to be the date of the file exported. so everyday we get a file with
unique name, and the dts supposed to run automatically everynight, but since
the file name is varied each night, how do solve this problem in DTS. Any
input will be appreciated.

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

DTS. The problem I am having now is that the naming schema of raw data file
is going to be the date of the file exported. so everyday we get a file with
unique name, and the dts supposed to run automatically everynight, but since
the file name is varied each night, how do solve this problem in DTS. Any
input will be appreciated.
You could add an ActiveX script task to modify the file name on the text
file connection.

I'd probably do it something like this:

1. Set up the package with these items in it (do it in this order and link
them up to flow in this order):

- ActiveX Script Task (leave empty for now)
- Dynamic Properties Task (ditto)
- Text File Connection to your prototypical text file (must have at least a
few sample lines of the right data in the right format with the right
delimiters or field widths).
- Database Connection to your target DB
- Your DTS Data Transformation Task from the Text File Connection to the
Database Connection (do the mappings).
- A Global Variable called InputTextFileName of type string (right-click in
some unoccupied part of the package and pick PackageProperties and the
Global Variables tab to see/create/set Global Variables).

2. Code the ActiveX Script Task to set the Global Variable InputTextFileName
to the correct filename using the builtin date, datepart, etc functions.

3. Set the Dynamic Properties task to reset the Text File Connection's
DataSource property to the value of the Global Variable InputTextFileName.

The ActiveX script task will set the InputTextFileName global variable to
the right string, the Dynamic Properties task will set the connection's data
source to that string and then the transformation task will pump the data
from the right file to your table.

There's probably help for all these techniques on WWW.SQLDTS.COM