SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenation through SSIS


Concatenation through SSIS

Author
Message
debanjan.ray
debanjan.ray
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 189
I am trying to do the required scenario through SSIS.

Input flat file:

id | name
1 a
2 b
1 c
1 cd
2 xyz
3 mno


Output flat file:

id | name
1 a, c, cd
2 b, xyz
3 mno

Can anyone guide me how to acheive this by SSIS transformation and tasks?

Thanks and regards,

Debanjan
Dave.Valentine
Dave.Valentine
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 240
Hi Debanjan,

You can do this a couple different ways. You can use and XML PATH query with a DB Source, or you can get creative with a script component. See the following blog post.
http://microsoft-ssis.blogspot.com/2012/10/creating-comma-separated-list-of.html

Dave
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41830 Visits: 14413
If you have to do flat-file to flat-file then using a Script Task might be the only option and the article linked to above is how I would do it. Just be careful, if your source flat-file is very large, the sort required before you can start doing the concatenation is a size-of-data operation meaning the entire flat-file will need to be in memory before the sort can begin and that can ause performance issues if you do not have the amount of RAM to accommodate the file plus the size required to do the sort. If that is the case you may want to consider putting this data into a database first, then re-extracting it to your output file. It's more network traffic and logging in the database but it may be more efficient in the end.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
debanjan.ray
debanjan.ray
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 189
Thanks All !!!
It works.... Smile
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41830 Visits: 14413
debanjan.ray (3/22/2013)
Thanks All !!!
It works.... Smile

What works? Common courtesy is to post the solution that worked for you so others that encounter the same issue and find this thread can benefit as well.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
tmitchelar
tmitchelar
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 508
Dave.Valentine (3/20/2013)
Hi Debanjan,

You can do this a couple different ways. You can use and XML PATH query with a DB Source, or you can get creative with a script component. See the following blog post.
http://microsoft-ssis.blogspot.com/2012/10/creating-comma-separated-list-of.html

Dave


Recursive CTE would work similarly. Personally - I'd just stage the data and then extract it using the suggestion that Dave mentions or a recursive CTE. I always like staging my flat files before any transformations occur just because it's so much easier to troubleshoot things.
debanjan.ray
debanjan.ray
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 189
Hi All,

Both the ways suggested by Dave, like using script component and putting all the data to a table then applying XML Path option worked for me.

As suggested by all, we opt for second option.

Thanks all for your suggestion... Smile

Debanjan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search