Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenation through SSIS Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 4:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:15 AM
Points: 31, Visits: 162
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
Post #1433121
Posted Wednesday, March 20, 2013 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:22 PM
Points: 14, Visits: 173
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
Post #1433275
Posted Wednesday, March 20, 2013 11:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 7,098, Visits: 12,605
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
Post #1433366
Posted Friday, March 22, 2013 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:15 AM
Points: 31, Visits: 162
Thanks All !!!
It works.... :)
Post #1434263
Posted Friday, March 22, 2013 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 7,098, Visits: 12,605
debanjan.ray (3/22/2013)
Thanks All !!!
It works.... :)

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
Post #1434297
Posted Friday, March 22, 2013 11:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
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.
Post #1434442
Posted Monday, March 25, 2013 12:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:15 AM
Points: 31, Visits: 162

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... :)

Debanjan
Post #1434759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse