Concatenation through SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Concatenation through SSIS

Concatenation through SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

debanjan.ray

Posted Wednesday, March 20, 2013 4:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:30 AM
Points: 27, Visits: 91

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

Dave.Valentine

Posted Wednesday, March 20, 2013 8:53 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:05 PM
Points: 14, Visits: 118

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

opc.three

Posted Wednesday, March 20, 2013 11:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 6,698, Visits: 11,726

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

debanjan.ray

Posted Friday, March 22, 2013 7:26 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:30 AM
Points: 27, Visits: 91

Thanks All !!!
It works.... :)

Post #1434263

opc.three

Posted Friday, March 22, 2013 8:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 6,698, Visits: 11,726

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

tmitchelar

Posted Friday, March 22, 2013 11:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:25 AM
Points: 121, Visits: 414

Dave.Valentine (3/20/2013)

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

debanjan.ray

Posted Monday, March 25, 2013 12:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:30 AM
Points: 27, Visits: 91

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 »

Permissions