I created a DTS from a txt file and I transform the file to skip some row
with condition. The txt file is in fact a report that has header, detail and
footer and I'm importing only the detail part of the report but would like to
have part (the BRANCH number) of the header as a field. Here's the sample
report

--------------------------------------------------------------------------------
page1 amortization report
12/10/2004
for branch 9125 bidon cie

code asset# descr dep meth book val dep val
T 390000 Dell server SLN 12,000.25 4,500.35
U 580000 Building SLD 50,000.36 49,000.36
......

page2 amortization report
12/10/2004
for branch 8237 bidon cie

code asset# descr dep meth book val dep val
T 391000 Workstation SLN 2,000.25 500.35
U 588000 Loader SLD 30,000.36 29,000.36


End of the repor
-----------------------------------------------------------------------------------

the data I want to insert are:
char char char char reel
reel Branch
T 390000 Dell server SLN 12,000.25 4,500.35
9125
U 580000 Building SLD 50,000.36 49,000.36
9125
T 391000 Workstation SLN 2,000.25 500.35
8237
U 588000 Loader SLD 30,000.36 29,000.36
8237


Thanks for your help

I would read the file using VBScript to start with (or even use your
favourite language outside of DTS and pass in the value) and extract the
value for the branch number.
I would assign this value to a global variable
You can then assign the global variable to a destination column.

Now back to structure.

The problem with what you have here as I see it is that you have a number of
"files" in a file. DTS will not like this at all. Sure you can skip a
header but by the look of it you have many.

You could choose to skip the next header rows by not inserting the row if
you see some text in that row but this will be remarkably clumsy.

Personally I would either have the feed provider give you the feed 1 file
per Bracnh number or I would parse the file again using whatever you want
and write out your own text files for each branch and then loop over them.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


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

I see, but if there is a way to not reset the variable for each row I think I
could make it.
something like

If DTSSource("Col002") = "36" then Branch = "9125"

"Allan Mitchell" wrote:

I agree but is there a way to reset the variable only if it meets certain
criteria let says that my branch number is beiing import at the begining of
the col004 every 50 lines then it sets the variable with the col004 value for
the next 49 rows then the variable is beiing reset to the new col004 value
for the next 49 rows something like a do while stuff.

Thanks fred



"Allan Mitchell" wrote:

Sure you can reset a Global variable whenever you want.

Does Branch = a global variable?

if it does then it needs to be DTSGlobalVariables("Branch").Value

This way would suggest you are reading the file anyway so you would not need
a global variables.

Personally if this was a common format for my files I would look to create a
custom task to do this. I would pass the filename and the destination table
to the task and from there my task would do the rest using VB.


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


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

Thanks for your help it works now by reseting the variable

"Allan Mitchell" wrote: