Post reply

whle loop in ssis?

  • January 23, 2012 at 9:29 am

    #263232

    Hi friends,

    i have three column named ModID,RecType,Comments having data's like below

    ModID RecType Comments

    1000 1 </p> DueAmount</p>

    1000 1 </p> Charge </p>

    1000 1 </p> Paid</p>

    1001 1 </p> Clarified</p>

    1002 0 </p> Ceased</p>

    and my need is if the ModID is same , the result needs to be in a single row...i mean, as you see ModID and RecType is similar for 1000, the result must be single record with all the comments concatenated under a singe row...

    i.e 1000 1 Concatenation of comments.......

    so i need to check the ModID column for repeated ID's, so i need while loop condition in ssis rather than T-SQL query..is this possible in ssis with out having T-SQL statement..?

    Help me friends..

    Thanks,
    Charmer

  • January 23, 2012 at 2:18 pm

    #1437376

    You'll want to use the FOR XML trick.

    This article will walk you through it:

    http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 23, 2012 at 6:21 pm

    #1437439

    It's not a WHILE loop, but you can accomplish the same effect using the FOREACH control container. Just point it at the table, and start building.

    Here's a good place to start with it:

    http://sqlblog.com/blogs/louis_davidson/archive/2007/01/19/ssis-foreach-looping-through-a-recordset.aspx

    (use the links to Brian's tips)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 23, 2012 at 6:41 pm

    #1437443

    Charmer (1/23/2012)

    so i need to check the ModID column for repeated ID's, so i need while loop condition in ssis rather than T-SQL query..is this possible in ssis with out having T-SQL statement..?

    Apologies, I missed this part of your requirement, figuring you could go with the easy method. The ForEach loop mentioned above is your best bet then.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 23, 2012 at 7:20 pm

    #1437447

    I have to ask... is a FOREACH loop in SSIS going to be as effecient as creating a result set in a stored procedure that uses FOR XML PATH() and a GROUP BY?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 23, 2012 at 7:31 pm

    #1437449

    Jeff Moden (1/23/2012)

    I have to ask... is a FOREACH loop in SSIS going to be as effecient as creating a result set in a stored procedure that uses FOR XML PATH() and a GROUP BY?

    No. Really you're going to want to take these into a transformation script component from an ordered set and kick out completed components back to the stream. That's the most efficient way and could outperform FOR XML if setup properly. However, the lack of allowance of T-SQL tells me that he's not pulling from an OLEDB backend and thus can't ensure an ordered set into the stream. I avoid using sorts like the plague once I'm in SSIS, they're memory hogs and they interrupt the stream.

    However, this technique is really not the best way to work with the ForEach component. The ForEach from a recordset is usually best used as an iterative proc caller, for smaller subsets of data. It's not really meant to handle a full stream's worth of data. It's the best workaround for what I've assumed and he's described though.

    My personal preference would be to dump to a staging table and then use FOR XML.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 24, 2012 at 2:56 am

    #1437533

    Evil Kraig F (1/23/2012)

    Jeff Moden (1/23/2012)

    I have to ask... is a FOREACH loop in SSIS going to be as effecient as creating a result set in a stored procedure that uses FOR XML PATH() and a GROUP BY?

    No. Really you're going to want to take these into a transformation script component from an ordered set and kick out completed components back to the stream. That's the most efficient way and could outperform FOR XML if setup properly. However, the lack of allowance of T-SQL tells me that he's not pulling from an OLEDB backend and thus can't ensure an ordered set into the stream. I avoid using sorts like the plague once I'm in SSIS, they're memory hogs and they interrupt the stream.

    However, this technique is really not the best way to work with the ForEach component. The ForEach from a recordset is usually best used as an iterative proc caller, for smaller subsets of data. It's not really meant to handle a full stream's worth of data. It's the best workaround for what I've assumed and he's described though.

    My personal preference would be to dump to a staging table and then use FOR XML.

    Hi friend,

    my source is oledb only...

    Could you gimme some samples to do this in script component..?

    i am not a script writer at all..but it seems this can be done very easily with script component...

    if it is not possible by any other option, i would go for T-SQL Statement...

    Thanks,
    Charmer

  • January 24, 2012 at 8:23 am

    #1437714

    Jeff Moden (1/23/2012)

    I have to ask... is a FOREACH loop in SSIS going to be as effecient as creating a result set in a stored procedure that uses FOR XML PATH() and a GROUP BY?

    It would depend on what you plan on doing with the concatenated result. Using the ForEach container would be roughly equivalent to creating a CLR concantenation, so if all you need to do is the concatenation is to put it back into a recordset and return it to a user, you've travelled a long path to go only a short distance.

    If on the other hand, you're using this to crank out a couple of hundred XML documents you happen to be saving to the OS, then no - it will probably end up being faster than the old forXML trick (and without some of the XML encoding issues).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 24, 2012 at 11:35 am

    #1437857

    Charmer (1/24/2012)

    Hi friend,

    my source is oledb only...

    Than FOR XML is probably best. Why avoid the easy method?

    Could you gimme some samples to do this in script component..?

    i am not a script writer at all..but it seems this can be done very easily with script component...

    if it is not possible by any other option, i would go for T-SQL Statement...

    I don't have any samples handy and it's simple only once you're used to working with asynchronous script components. However, I can walk you through the process generically and you can make your own decisions from there.

    First you'd start with OLEDB source component, and make sure your data pull is ORDER BY'd based on how you want to create your delimited string. Then, in the Advanced Editor, change your IsSorted operator to TRUE:

    From there, you want to use a Script Component - Transformation as the next component. You'll want to make it asynchronous, which means you don't associate the output stream to an inbound stream's ID.

    On the RowIn, you'll build out your delimited string as each row comes in, using package variables to persist the settings between each inbound row. When you change one of the grouping items, you fire off a row back into the stream with your delimited string rebuilt. From there you treat it as though it came from a regular source.

    This isn't a simple task, and you need to be familiar with the internals of the script component in this regard.

    This is a pretty solid walkthrough of exactly what you're looking to do, but uses C# instead of VB.NET so up to your comfort zone:

    http://www.rad.pasfu.com/index.php?/archives/19-How-to-use-Script-Component-as-Asynchronous-Transformation.html

    Here's another one that looked pretty reasonable:

    http://www.codeproject.com/Articles/193855/An-indespensible-SSIS-transformation-component-Scr

    As you can see, this particular solution is more than I can easily discuss in a forum post.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 25, 2012 at 1:40 am

    #1438129

    Evil Kraig F (1/24/2012)

    Charmer (1/24/2012)

    Hi friend,

    my source is oledb only...

    Than FOR XML is probably best. Why avoid the easy method?

    Could you gimme some samples to do this in script component..?

    i am not a script writer at all..but it seems this can be done very easily with script component...

    if it is not possible by any other option, i would go for T-SQL Statement...

    I don't have any samples handy and it's simple only once you're used to working with asynchronous script components. However, I can walk you through the process generically and you can make your own decisions from there.

    First you'd start with OLEDB source component, and make sure your data pull is ORDER BY'd based on how you want to create your delimited string. Then, in the Advanced Editor, change your IsSorted operator to TRUE:

    From there, you want to use a Script Component - Transformation as the next component. You'll want to make it asynchronous, which means you don't associate the output stream to an inbound stream's ID.

    On the RowIn, you'll build out your delimited string as each row comes in, using package variables to persist the settings between each inbound row. When you change one of the grouping items, you fire off a row back into the stream with your delimited string rebuilt. From there you treat it as though it came from a regular source.

    This isn't a simple task, and you need to be familiar with the internals of the script component in this regard.

    This is a pretty solid walkthrough of exactly what you're looking to do, but uses C# instead of VB.NET so up to your comfort zone:

    http://www.rad.pasfu.com/index.php?/archives/19-How-to-use-Script-Component-as-Asynchronous-Transformation.html

    Here's another one that looked pretty reasonable:

    http://www.codeproject.com/Articles/193855/An-indespensible-SSIS-transformation-component-Scr

    As you can see, this particular solution is more than I can easily discuss in a forum post.

    Hi Friend,

    i have gone through the link..the second link is slightly what i am looking for...

    In there, they are splitting the rows...but i need to combine the rows if the condition what i give is true...

    i don't see any if condition to check....the condition i have posted earlier...i hope you could see that what it was...

    i need a condition to check for any duplicate ID's in the same column..

    if you say, XML is the best way , i would try with that then...

    Thanks for the response

    Thanks,
    Charmer

  • January 25, 2012 at 6:25 pm

    #1438522

    Apologies for the distraction from "Charmer's" questions... I just want to say "thanks" to Craig and Matt for the feedback on my question.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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