SSIS IF POSSIBLE?

Question

SSIS IF POSSIBLE?

TONYAPPL

Newbie

Points: 3
More actions
April 3, 2012 at 8:16 pm

#254264

Hi I am new to SSIS ,
I have this incoming sample flat file
PO1123456,20101231,4710,1,5.67
PO1123456,20101231,4521,3,7.86
PO1123456,20101231,101,8,5.45
PO1123457,20101230,40,4,7.86
PO1123457,20101230,50,5,5.45
This is the desired
H,ZEDI,850,PO1123456,20111231
D,4710,1,5.67
D,4521,3,7.86
D,101,8,5.45
H,ZEDI,850,PO1123457,20111230
D,40,4,7.86
D,50,5,5.45
where H , D , ZEDI and 850 will be inserted in .
Is this possible , Thanks In advance.
Mark

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

The values H, ZEDI and 850 should be inserted for every row, this can be achieved using a DERIVED COLUMN transformation in SSIS

Raunak J

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Since these extra values are only inserted for the first occurence of a new value in the first column, you'll need a script component. In the script, you'll keep track what is the current value of the first column. If it changes, you add the extra values. However, the number of columns need to be consistent, so you'll need to add blanks for all the other rows.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Try this a no script component/task solution

Insert the data with 3 derived columns each having a default value as stated. No evry row will have the default values for 3 cells.

Now using a CTE fire a ROWCOUNT() query against the table and partition on the columns having such "PO1123456" values

Now issue an update command on the CTE and SET values of all rows for cell 1,2 and 3 to " " where ROWNUMBER = 2 and 3

Raunak J

ACinAZ SSCommitted Points: 1849 More actions · Answer 4

I would think you'd want to have the script component to keep track of what PO value you're processing (as was suggested earlier), then a split/loop to write the header records ('H') in one path and the detail ('D') in another; do "header" once and then loop on the incoming records until the PO value changes to write all of the detail rows. When the PO value changes, break the loop, reset your latch value in the script and loop until the next value. Lather, rinse, repeat...