Ive beeb trying unsucseffully to use a dts to import this file into two tables

"200001"
"999999"
"75"
"999999"
"004"
"24/03/2005"
"001"
"Fhead (AT) office (DOT) com"
"me (AT) anywhere (DOT) com"
"200001 DebBacsFM Serial: 4 AuthCode: 9999"
---- bank records
"12345612345678ANYPERSON REFERENCE 0000000496117"
ETC
-- end of bank records
"TRAILER RECORD"


Lines 1 - 10 i use an activex task to read the file and assign the values to
global variables , as i need to retreive data based in thcontants. Line 3
shows how meny banking records are contained in the file. The bank records
need to be put in a different table along with a global variable. After the
bank records there are 8 more records. my problem is when i set a Text File
Source connection for the banking transaction import i set skip rows to 10
so i can set the fixed width of the fields. this works fine when i use the
test function, how ever when setting the datapump using the following code

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************
Function Main()
IF (DTSGlobalVariables("gv.TranCounter").value <= CINT(
DTSGlobalVariables("FH_TransactionCount").Value)) THEN
DTSDestination ("BTYear_DestSortCode") = DTSSource("Col002")
DTSDestination ("BTYear_DestAccountNumber") = DTSSource("Col003")
DTSDestination ("BTYear_DestAccountName") = DTSSource("Col004")
DTSDestination ("BTYear_DestBankRef") = DTSSource("Col005")
DTSDestination ("BTYear_TransValue") = DTSSource("Col006")
DTSDestination ("BTYear_TransCode") = DTSSource("Col007")
DTSDestination ("BTYear_LedgerKey") = DTSGlobalVariables("LedgerKey")
DTSDestination ("BTYear_Licence") =DTSGlobalVariables("FH_Licence")
DTSDestination ("tranref") =DTSGlobalVariables("gv.TranCounter").value
END IF
DTSGlobalVariables("gv.TranCounter").value =
Cint(DTSGlobalVariables("gv.TranCounter").Value +1)
Main = DTSTransformStat_OK
End Function

Im always missing the first 10 rows of banking data. it looks like as ive
opened the file and read X amount of lines, when the transformdata task firs
it picks up the file from the last read point , skips the amount of rows in
the skip rows vlue and carrys on importing. I also can not get it to stop
importing after its read all the banking records. Please note thet the begin
and end banking records are labels i have put in to show the seperat sections
of the file.

I would be interst to know what the soloution to this is, and if its
possible to have 1 text file source and get it to read x amount of likes into
one table and x amount to another, with varying record lengths . any easy
examples would be most appricated

ta

I am not sure I get what you mean but let me try

You have a file which has 10 header rows of info.
You want to skip these so you set the file connection to skip the first
10 rows.

You then try to import.

On importing the task has skipped the 10 header rows AND the first 10
lines of data?

In your code it looks as though you have read off how many transactions
there should be and are comparing that to another variable.

You do reset that variable before the datapump each time right?


Allan

"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

Allen,

Im not sure what you mean, the first activex task reads off the 10 headers
records, before i move onto the transform data task. If i have the skip rows
set to 0 on the text file source i get all the 10 headers included, but if i
set skip rows to 10 it skips the first 10 headers and the first 10 ba
nking records . Im presuming its something to do with the fact im using a
fixed length record in the text source and the records in the headers are a
different length to the banking records ?

"Allan Mitchell" wrote:

That was exactly what I meant.


I created a text file

rubbish
Aa
ABC
A
XYZ
12

The only data here is 12 which is two columns of one char

I set the connection to skip the first 5 lines and no headers in first
row and this imported one row.

Have a look at your file in a HEX editor and see if the lines are
terminated correctly.

Allan

"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

Allen,

I have tried that myself an it works fine, how ever before i get to that
stage i open the file in an activex task which opens the file and reads the
first ten lines. would that cause the problem with the skip rows ??


"Allan Mitchell" wrote:

So long as you remember to close I cannot imagine why it would cause the
problem no.
What you are saying is that the DataPump not thinks those first 10 rows
have gone and it needs to ignore lines 11-20

Allan

"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

Allan,

I will hav another look at my coding when i get into the office tomorrow. I
will let you know if i still have problems

Thanks for all your help

"Allan Mitchell" wrote:

Allen,

I think i have finally got to the bottom of the problem with te importing of
the file. It has to do with fact that im using the fixed width option in the
text File Source properties. In the banking transaction ection there are 8
fields as follows
1 = " Length 1
2 = Sort Code Length 6
3 = Acc Number Length 8
4 = Acc Name Length 18
5 = Reference Length 18
6 = Value Length 11
7 = TransTtype Length 2
8 = " Length 1


It looks as if the transform data is using the records from the first 10
lines to make up the fixed lengh of the normal records

Is there some coding i can put in the Transform Data task, to ignore the
first 10 lines of the file, then set the formatting for the fields to the
right length for the banking records.

The file is fixed in this format, and speed to import the file into the
tables is important as clients will be online waiting for reports. any
suggestions on where to go frm here ?


"Peter Newman" wrote: