I am importing a text file into SQL using DTS. The text file has a WBS
(work breakdown structure) key that I need to do some magic on. I need to
parse the WBS_KEY and generate an ID/Parent relation for every row in the
table. I have no clue on what/how to do this.

Text file has this structure:
WBS_KEY, WBS_DECSRIPTION,PATTERN
ANC1,blah,XXXX.XXXX.XX.XX.XX
ANC1.1000,blah,NULL
ANC1.1000.01,blah,NULL
ANC2,blah,XXXX.XXXX.XX.XX
ANC1.2000,blah,NULL
ANC1.2000.01,blah,NULL
ANC2.1000,blah,NULL
ANC1.1000.01.01,blah,NULL
IK,blah,XX.XXXX.XX.XX
IK.1001,blah,NULL
IK.1002,blah,NULL
IK.1001.01,blah,NULL


I have DTS creating the WBS_ID (IDENTITY, PK) and WBS_PARENT fields after
importing. How do I produce the following?
WBS_ID,WBS_PARENT,WBS_KEY, WBS_DECSRIPTION,PATTERN
1,0,ANC1,blah,XXXX.XXXX.XX.XX.XX
2,1,ANC1.1000,blah,NULL
3,2,ANC1.1000.01,blah,NULL
4,0,ANC2,blah,XXXX.XXXX.XX.XX
5,1,ANC1.2000,blah,NULL
6,5,ANC1.2000.01,blah,NULL
7,4,ANC2.1000,blah,NULL
8,3,ANC1.1000.01.01,blah,NULL
9,0,IK,blah,XX.XXXX.XX.XX
10,9,IK.1001,blah,NULL
11,9,IK.1002,blah,NULL
11,10,IK.1001.01,blah,NULL
and so on (1400 rows total)

Ok So the first thing you are going to need to do is order the data
otherwise it will be a pig to find out what relates to what. Here you
will order by WBS_KEY.

Now in the AX Script Transform for WBSParent you keep an incrementing
local variable. Evertime the pattern at the start of WBS_KEY Changes it
resets to 0

In your example it will change when the pattern moves from ANC1 to ANC2
and then to IK

This should help you to get started

Allan

"HB" <post (AT) group (DOT) com> wrote