Hi guru's

I have a DTS package that creates a series of text files
at the end of each month in order to send data dumps to
our customers.

I had hoped to fully automate this process except that I
cannot seem to dynamically change the file name and
destination of the text file!

I currently have set up:
Source:
SQL Server 2000 OLE DB Connection
Get Data:
Transform Data Task executing a stored procedure with a
date passed in as a parameter (this date is stored in a
Package Global Variable which is set using a Dynamic
Properties Task),
Destination:
Text File (Destination) Connection

I have tried setting the Text File (Destination)
Connection properties using the Dynamic Properties Task
but it just fails unless I type in an existing text file
name.

At present I am manually changing the names of the
destination files then having to go into the
Transformation to change the destination there also.

There must be a way of dynamically changing this each
month? I can dynamically change all other types of
destination, so why not text files?

Hope someone out there can help me! I'm pretty good with
SQL & TSQL but nothing like VB so please bear this in mind
when responding.

Many thanks in advance for your help.

Regards

Paula

This article always seems to work for me

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.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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

Thanks Allan.
I also found this article and tried to implement it.
Though I don't really do VB Scripts I did manage to
understand this one and think I've updated it for my own
purposes, but now I don't know where to put it in my
DTS. Can you advise me on this?

Also, will I have a problem with the destination on the
Transformation? Previously, when changing the output
destination dynamically to Access or SQL databases I have
had to set the destination in the Transformation to a
constant? Maybe I'm just looking at the whole thing
wrong?

OK Your package will look like this (it may not but you'll get the idea)

Active Script Task --> On Success --> Source Whatever ---Pump---> Text File


The script will go in the Active Script task.

It will do everything for you.





--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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

Ooh Allan, I could kiss you!!! X

I've always been scared to death of ActiveX VB scripting
but this was a doddle and works excellently!

Thanks for your prompt help! Don't know what I would
have done without you (yes I do - I would have taken 3
hours every month updating DTS destinations!)

Luv

Paula

Paula.

after the day I've had..................

Glad we could help

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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

Alan,

The example on the web site works, but I can't get a dynamic properties method to work. Can you show me an example of this?

Thanks!

Tom

"Allan Mitchell" wrote:

How are you sourcing the text file name?
You need to have the DP task execute first in the package.

Let me know the answer to #1 and i can send you a succesful version (will
need eMail address)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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