Tuesday, February 14, 2012

Data Flow: Converting data in multiple columns

Hi,

I'm just wondering what's the best approach in Data Flow to convert the following input file format:

Date, Code1, Value1, Code2, Value2

1-Jan-2006, abc1, 20.00, xyz3, 35.00

2-Jan-2006, abc1, 30.00, xyz5, 6.30

into the following output format (to be loaded into a SQL DB):

Date, Code, Value

1-Jan-2006, abc1, 20.00

1-Jan-2006, xyz3, 35.00

2-Jan-2006, abc1, 30.00

2-Jan-2006, xyz5, 6.30

I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.

Ok, I have found a method to get what I wanted, but I'm not sure if it's the best approach. Any comments appreciated.

I first used a Multicast and feed the input data into 2 Script Components. The first Script Component has input columns of Date, Code1 & Value1, while 2nd Script Component has input columns of Date, Code2 & Value2. In both Script Components, the output columns are Date, Code & Value.

The output of each Script Component are then connected to a Sort and both Sort goes to a Merge. The output from the Merge will then go to a OLE DB Destination to be loaded into a SQL DB (not 2005 version).

Hope it makes sense.

|||

I think Unpivot tranformation cam deliver the same funcionality you put in your script components; but the multicast; the sort and merge join would be required anyway.

Rafael Salas

|||

Hi,

Why dont you have a union step feeded twice by the multicast

then

Delete and add lines so that you would have the following

Date date date

Field 2 cod1 cod2

Field 3 value1 value2

At the end you should have what you want,

Regards,

No comments:

Post a Comment