I am going to import a textfile from a mainframe system that is formatted
with fields in fixed column positions. But there is another thing that
bothers me, every record in the file is split up on four rows in the file. I
want to use fields from the 4 lines and compose input for a single row in a
database.

Anyone out there who knows how to perform this multi-line record reading of
a textfile?

Or should I avoid DTS and go for a C# application because of the multi-line
input records?

Best regards,
Per Salmi

Per,

Try this. Create a table with a numeric column for row enumeration. Have a
global variable initialized with 1 before extract starts. Extract the file
using data pump. Assign the variable to the enumeration column and increment
variable in the same ActiveX transformation. Later on use enumeration to
parse a record out of four consecutive rows.

Ilya
"Per Salmi" <per.salmi (AT) nospam (DOT) nospam> wrote

I'm not sure if I understand what you mean by that. Would it end up with a
temporary table where I have all the rows from the text file with the
enumeration number set to the same value for each group of 4 lines? Or will
it result in a table with a numbered sequence of lines that I process
outside of DTS to build a record out of each group of 4 lines?

/Per

"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> skrev i meddelandet
news:%23cdtuI6qEHA.3900 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

If it results in a new enumeration number for each line... Wouldn't it be
easier to use an identity column that automatically increases by 1 in the
temporary table.

Still wondering about how to get 4 rows and process them into one row when
the text lines are in the database... Seems hard to do it in DTS...

/Per

"Per Salmi" <per.salmi (AT) nospam (DOT) nospam> skrev i meddelandet
news:ObEy0%236qEHA.644 (AT) tk2msftngp13 (DOT) phx.gbl...

OK

You have an enumerator variable in your Active Script transform. Its values
are 1-4

You grab the values from each row of your text file

For values 1-3 you do not insert anything to the DB

For 4 you can then issue an insert with the values you have accumulated over
the last 4 rows of the file

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Per Salmi" <per.salmi (AT) nospam (DOT) nospam> wrote

Per,

You would have a table with enumeration incremented for each row. From there
you can group records in fours. A regular identity column does not guaranty
numbers generated being strictly sequential.

Ilya

"Per Salmi" <per.salmi (AT) nospam (DOT) nospam> wrote

can u send some example lines from ur text file ? at least 10 lines


"Per Salmi" <per.salmi (AT) nospam (DOT) nospam> wrote

Here is the solution that I figured out, if anyone is interested.

The tip about using a global variable enumerator to keep track of the line
(1 - 4) within the record I am reading was good. Thanks Ilya & Allan!

Then I created a activex script transform that has the full text column as
input and all the database columns as output.

In the script I put a Switch Case statement that based on the value of the
global enumerator, extracts the interesting parts of the source data and
sets the destination fields. After this it returns
DTSTransformStat_SkipInsert for the first three lines. Only the 4th line
returns DTSTransformStat_OK and this results in one insert for every 4 lines
and the field contents seems to be correct.

Here is a sample:

Function Main()

Select Case DTSGlobalVariables("RowEnum").Value
Case 1 ' Get fields on the first line of the multi line record
DTSDestination("Col1FromRow1") = Left(DTSSource("Col001"), 6)
DTSDestination("Col2FromRow1") = Mid(DTSSource("Col001"), 7, 6)
DTSDestination("Col3FromRow1") = Mid(DTSSource("Col001"), 13, 10)
DTSDestination("Col4FromRow1") = Mid(DTSSource("Col001"), 23, 13)
DTSGlobalVariables("RowEnum").Value = DTSGlobalVariables("RowEnum").Value
+1
Main = DTSTransformStat_SkipInsert

Case 2 ' Get fields on the secondline of the multi line record
DTSDestination("Col1FromRow2") = Left(DTSSource("Col001"), 35)
DTSDestination("Col2FromRow2") = Mid(DTSSource("Col001"), 36, 30)
DTSDestination("Col3FromRow2") = Mid(DTSSource("Col001"), 66, 30)
DTSGlobalVariables("RowEnum").Value = DTSGlobalVariables("RowEnum").Value
+1
Main = DTSTransformStat_SkipInsert

Case 3 Get fields on the thirdline of the multi line record
DTSDestination("Col1FromRow3") = Left(DTSSource("Col001"), 5)
DTSDestination("Col2FromRow3") = Mid(DTSSource("Col001"), 6, 8)
DTSGlobalVariables("RowEnum").Value = DTSGlobalVariables("RowEnum").Value
+1
Main = DTSTransformStat_SkipInsert

Case 4 Get fields on the last line of the multi line record
DTSDestination("Col1FromRow4") = Left(DTSSource("Col001"), 6)
DTSDestination("Col2FromRow4") = Mid(DTSSource("Col001"), 7, 6)
DTSDestination("Col3FromRow4") = Mid(DTSSource("Col001"), 13, 6)
DTSGlobalVariables("RowEnum").Value = 1
Main = DTSTransformStat_OK

End Select

End Function

Best regards,
Per Salmi



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> skrev i meddelandet
news:OHU5na9qEHA.2636 (AT) TK2MSFTNGP09 (DOT) phx.gbl...