Post reply

Concatenation through SSIS

  • March 20, 2013 at 4:32 am

    #291256

    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

  • March 20, 2013 at 8:53 am

    #1598952

    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

  • March 20, 2013 at 11:30 am

    #1599033

    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

  • March 22, 2013 at 7:26 am

    #1599765

    Thanks All !!!

    It works.... 🙂

  • March 22, 2013 at 8:24 am

    #1599795

    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

  • March 22, 2013 at 11:32 am

    #1599923

    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.

  • March 25, 2013 at 12:30 am

    #1600181

    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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply