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

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



Wed, 05 Nov 2003 05:23:05 GMT
 Importing a Text File via DTS and adding the File Name as a column via DTS


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/



Wed, 05 Nov 2003 18:09:29 GMT
 Importing a Text File via DTS and adding the File Name as a column via DTS
Thanks Darren - I'm going to give this a shot and keep you apprised of the
results/solution.

Baris


Quote:

> 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/



Fri, 07 Nov 2003 02:10:06 GMT
 
 [ 3 post ] 

 Relevant Pages 

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

2. Importing unicode CSV file via DTS

3. Importing unicode CSV file via DTS

4. import file via DTS with IDENTITY

5. XML file import via DTS withOUT Web Release 1

6. Import excell file in sql table via dts

7. Loading a Text File Through DTS via ASP Page

8. Append To Text File via DTS

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

10. DTS, Importing Text File, Truncates txt column

11. DTS Export to Text File with File Name Prompt

12. DTS Change the file name of a TEXT file


 
Powered by phpBB® Forum Software