I am relatively new to DTS jobs, and for the most part, have just used
the Import and Export wizards to get data in and out of things. I do
OK writing SQL - but by no means am an expert...

Here's my problem.
I have a log file I need to import every day. The problem is I need to
change the name of the source file every day. Being a log file, the
file name contains a static string and a date value - so it can be
calculated. I could do this in Access, but I'm a little stumped in
SQLServer with DTS.

Thanks for your assistance...
Ray

The following is an example of how to do this:
How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?200

So however you figured out how to do it in Access, you would
use similar logic to construct the value for sFilename - the
variable for the file name in the example.

-Sue

On Wed, 11 Oct 2006 18:23:18 -0400, Ray
<ray_park (AT) bellsouth (DOT) net> wrote:

Ok - I'm having a problem - due to my own lack of
understanding...(sometimes referred to as stupidity)


--------------------------------------------------------------------------------
I had an existing package with 3 objects

Connection 1
DataSource: Text File (Source)
FileName: C:\MyDocs\MyFile20040501.txt

Transform Data Task

Connection 2
DataSource: Microsoft OLE DB Provider for SQL Server


--------------------------------------------------------------------------------
I added the ActiveX Task and put it before "Connection 1" in the
WorkFlow.

Created the FileName code and executed per the example.

Set oConn = DTSGlobalVariables.Parent.Connections("Connection 1")
oConn.DataSource = sFilename

Th ActiveX task completes successfully - but the "Connection 1" task
says it can't find the file - and displays the original file I
manually input into the connection when I created it by hand (MyFile)
- which leads me to believe the ActiveX code isn't changing the
filename....

The only odd part is that it seems to be "clobbering" the original
filename (sorry about using such a technical term) - because instead
of displaying "C:\MyDocs\MyFile20040501.txt" as the filename it can't
find, it's simply displaying "MyFile20040501".

Whew - So - what might I be missing - (other than a fully functional
brain)

Thanks Again
Ray


On Thu, 12 Oct 2006 06:21:52 -0600, Sue Hoegemeier
<Sue_H (AT) nomail (DOT) please> wrote:

ARRRRRRGGGGGGHHHHHH!!!

I hate Typo's -
It works great once everything is spelled right -
Thanks for the help!


On Thu, 12 Oct 2006 06:21:52 -0600, Sue Hoegemeier
<Sue_H (AT) nomail (DOT) please> wrote:

No problem....thanks for posting back.

-Sue

On Thu, 12 Oct 2006 14:29:52 -0400, Ray
<ray_park (AT) bellsouth (DOT) net> wrote: