Importing a Text File via DTS and adding the File Name as a column via DTS

Importing a Text File via DTS and adding the File Name as a column via DTS

Post by Baris Gu » Sun, 20 May 2001 06:23:05



Hello - I did a brief search on this subject and did not find anything in
this newsgroup.

Here's my problem:

I have a series of text files which all have the same format and structure.
They are files my company receives on a daily basis and uploads for use on
that day. The files do not feature a date field but the date they are for is
identifiable via the file name, e.g., EXT30_20010518.txt would be today's.

As is typical we now need to do historical analyses using these files but
since they do not feature a date column any uploaded files would be
meaningless in their current format. I need to add a date field and extract
the date from the file name. To your knowledge, can I do this via a DTS
transformation? (Even if I could simply get the file name in a column I
could do parsing in SQL).

Any tips, help or resources would be greatly appreciated. Thanks for your
time,

Baris

 
 
 

Importing a Text File via DTS and adding the File Name as a column via DTS

Post by Darren Gree » Sun, 20 May 2001 19:09:29



writes

Quote:>Hello - I did a brief search on this subject and did not find anything in
>this newsgroup.

>Here's my problem:

>I have a series of text files which all have the same format and structure.
>They are files my company receives on a daily basis and uploads for use on
>that day. The files do not feature a date field but the date they are for is
>identifiable via the file name, e.g., EXT30_20010518.txt would be today's.

>As is typical we now need to do historical analyses using these files but
>since they do not feature a date column any uploaded files would be
>meaningless in their current format. I need to add a date field and extract
>the date from the file name. To your knowledge, can I do this via a DTS
>transformation? (Even if I could simply get the file name in a column I
>could do parsing in SQL).

>Any tips, help or resources would be greatly appreciated. Thanks for your
>time,

>Baris

How are you setting the filename each day? You could derive this in an
ActiveX Script, use it to set the connection filename (DataSource
property), and store it in a global variable. Make sure the destination
table has a Data column and use an ActiveX Transformation to assign the
global variable to this column.

--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

Importing a Text File via DTS and adding the File Name as a column via DTS

Post by Baris Gu » Tue, 22 May 2001 03:10:06


Thanks Darren - I'm going to give this a shot and keep you apprised of the
results/solution.

Baris



> writes
> >Hello - I did a brief search on this subject and did not find anything in
> >this newsgroup.

> >Here's my problem:

> >I have a series of text files which all have the same format and
structure.
> >They are files my company receives on a daily basis and uploads for use
on
> >that day. The files do not feature a date field but the date they are for
is
> >identifiable via the file name, e.g., EXT30_20010518.txt would be
today's.

> >As is typical we now need to do historical analyses using these files but
> >since they do not feature a date column any uploaded files would be
> >meaningless in their current format. I need to add a date field and
extract
> >the date from the file name. To your knowledge, can I do this via a DTS
> >transformation? (Even if I could simply get the file name in a column I
> >could do parsing in SQL).

> >Any tips, help or resources would be greatly appreciated. Thanks for your
> >time,

> >Baris

> How are you setting the filename each day? You could derive this in an
> ActiveX Script, use it to set the connection filename (DataSource
> property), and store it in a global variable. Make sure the destination
> table has a Data column and use an ActiveX Transformation to assign the
> global variable to this column.

> --
> Darren Green
> SQL/DTS - http://www.swynk.com/friends/green/

 
 
 

1. Import multiple text files via DTS - example script?

Hi there. I have a large number of small text files (> 1000 files of about
50K each) which I would like to import into a single SQL Server 2000 table.
The files all have the same format, so I can do it via an append, but I'm
not sure how to set up a DTS job to take all of the files in one pass.

The files are in the same directory and have the filename format xxyyzz.txt.
They are identically structured. I know how to set up a DTS job to do one
but not how to either run it over and over for each file name or create one
big filespec like *.txt to take them all.

Any suggestions, sample code or links? Thanks in advance.

-- insomniac

2. Importing from flat text files to relational DB

3. Importing unicode CSV file via DTS

4. Find Triggers in SQL Server 2000

5. Database shrinking

6. import file via DTS with IDENTITY

7. Please Help. Error 3021 "No Current Record". VB Prof. v5

8. XML file import via DTS withOUT Web Release 1

9. Import excell file in sql table via dts

10. Loading a Text File Through DTS via ASP Page

11. Append To Text File via DTS

12. Table Update Via Text File (DTS, Stored Proc, ActiveX, VBScript)