I would like to use DTS to read(loop) multiple TEXT files which have
different record layouts. Inside the TEXT file there is a Header record and
multiple Detail records.


I need to Strip out of the Header record just one field(CustomerNumber),
then when I read each of the Detail records add in the SAVED
CustomerNumber to select Detail records which will make-up my load to the
Transformation(SQL Table).


Text file layout:

H,123456789,,,Date,something
D,BR549,,8/18/03,44.00,SC
D,BR550,,8/8/03,44.00,SD


Each of the text files will have a Header record and 1 to several Detail
records. The records(To Load) after manipulated needs to look something like
this:

123456789,BR549,8/18/03,SC
123456789,BR550,8/8/03,SD

I guess what I'm asking for is a way to save the CustomerNumber of the
Header record an use that for every Detail record.

What i would do is this

I would read the first line of the text file using an Active Script task and
extract the Customer Number. I would then assign that to a Global Variable
which you can use later in a DataPump task.

As for varying formats. I presume that each file has the same format within
and not multiple within one file.

If so then you have a couple of options

1. Put each different file format in it's own directory and have 1 data
pump per folder.
2. Rebuild the datapump each time to work with the file.**

**Not as simple as it sounds. You will need to read the file up front and
build each stage of the pump yourself. If there are many formats and many
files then you could be doing this for every file and that will make things
slow.

--

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



"Andy" <Hoosbruin (AT) Kconline (DOT) com> wrote

Do you have an example of what the ActiveX Script would look like..... The
CustomerNumber will always be the second field in the Header file.

I tried to use the examples in BOL for Global Variable defines but when I
run the DTS I get type mismatch...


Thanks for the help..


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

OK here we go. Text file looks like this

1,"I Want this Value",3,4,5


You get the idea


Instead of msgboxing it out you can assign to a GV instead


Function Main()


dim fso

dim tStream

dim strLine
dim arr

set fso = CREATEOBJECT("Scripting.FileSystemObject")


set tStream = fso.OpenTextFile("c:\ShowMe.txt",1)

StrLine = tStream.Readline

arr = Split(StrLine,",")

'0 based
msgbox arr(1)


tStream.Close

set fso = Nothing





Main = DTSTaskExecResult_Success
End Function



--

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



"Andy" <Hoosbruin (AT) Kconline (DOT) com> wrote