SQLServerCentral.com / Integration Services / Data Warehousing / Concatenation through SSIS / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:28:54 GMT20RE: Concatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspxHi 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... :)DebanjanMon, 25 Mar 2013 00:30:29 GMTdebanjan.rayRE: Concatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspx[quote][b]Dave.Valentine (3/20/2013)[/b][hr]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.htmlDave[/quote]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.Fri, 22 Mar 2013 11:32:39 GMTtmitchelarRE: Concatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspx[quote][b]debanjan.ray (3/22/2013)[/b][hr]Thanks All !!! It works.... :)[/quote][i]What[/i] 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.Fri, 22 Mar 2013 08:24:44 GMTOrlando ColamatteoRE: Concatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspxThanks All !!! It works.... :)Fri, 22 Mar 2013 07:26:56 GMTdebanjan.rayRE: Concatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspxIf 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.Wed, 20 Mar 2013 11:30:33 GMTOrlando ColamatteoRE: Concatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspxHi 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.htmlDaveWed, 20 Mar 2013 08:53:02 GMTDave.ValentineConcatenation through SSIShttp://www.sqlservercentral.com/Forums/Topic1433121-364-1.aspxI am trying to do the required scenario through SSIS. Input flat file: id | name1 a2 b1 c1 cd2 xyz3 mno Output flat file: id | name1 a, c, cd2 b, xyz3 mno Can anyone guide me how to acheive this by SSIS transformation and tasks?Thanks and regards,DebanjanWed, 20 Mar 2013 04:32:54 GMTdebanjan.ray