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: Wednesday, April 16, 2014 12:18 AM
Points: 31, Visits: 147
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: Tuesday, April 08, 2014 7:34 AM
Points: 14, Visits: 147
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: Wednesday, April 16, 2014 12:18 AM
Points: 31, Visits: 147
Thanks All !!!
It works.... :)
Post #1434263
Posted Friday, March 22, 2013 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: Thursday, April 10, 2014 2:17 PM
Points: 124, Visits: 485
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: Wednesday, April 16, 2014 12:18 AM
Points: 31, Visits: 147

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