asked
How to split single column data into multiple columns is SSIS
Hi All, I have a scenario where in I need to Split my input based on certain condition for which I'm using a conditional split in BIDS 2008. So I'm all fine until here, now on each of splits I need to create a set of 4 columns which will have data column structure like office_id1, office_id2, office_id3 and location . All the id columns will have data in them but might be with different locations. Currently I have all these in one column based on locations. I need to split id into into different columns in a dataset so that I can map them into the destination table accordingly. I'm trying derived column and union all but not able to do it as the mapping on the destination displays the input columns of the 1st derived column. Can I use merge join to do this or should I populate this into another table and then use another task to map the data. My Current table Structure: Officeid, Location 1 A 3 B 4 C I'm trying to achieve the following data set: office1, office2,office3, location 1 Null Null A Null 2 Null B Null Null 3 C Kindly advise the ideal route to do this.
***I'm trying derived column and union all but not able to do it as the mapping on the destination displays the input columns of the 1st derived column.*** Perhaps I'm not quite clear on what the issue is, but using the conditional split, derived columns, union all pattern should be fine. ![alt text][1] All the inputs into the Union All should be the same 4 columns. Are you creating all 3 columns in each of the separate derived column transforms and specifying NULL in 2 (of the 3) Office# columns? Here's what Location B (derived transform) looks like in my package... ![alt text][2] [1]: /storage/temp/494-capture.png [2]: /storage/temp/495-capture2.png
9 People are following this question.
