Hello,

I am trying to import log file from a monthly generated log using DTS. The problem is that the log file name changes every month and I want the text file (source) to change to the same file name.

I have created a DTS like this:

A. Set the current Text file path with a "Dynamic property task"
that changes the DTSTask_DTSDataPumpTask_1-SourceObjectName. I did it with a query like so:
select ('\\local\d$\logs\' +
cast(month(GetDate()) as varchar) +
cast(year(GetDate()) as varchar) +
'.log')
as log_file_name

B. Text File (source).

C. Calling "Transform data task".

The problem is that the source text file name does not change even though I validated my query result. When the DTS is executed it keeps refering the file that I stated when I created the text file name.
I stated a file name because It wouldn't allow me to continue without one.

My questions are:
A. Is this the currect way to handle this case?
B. Way doesn't the "Dynamic property task" change my source file name? Have I forgoten something?

Thank you in advance,
Guy

--
Message posted via http://www.sqlmonster.com

Have a look at our site

www.SQLDTS.com

in particular 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.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Guy via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

In message <#q9a55M8EHA.824 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Allan Mitchell
<allan (AT) no-spam (DOT) sqldts.com> writes
Also try setting Close Connection on completion for any task that uses
the text file connection before the change. If the connection is already
open, the change will not take effect, unless it is reopened.

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

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