Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS splitting records with Script Component

Answered SSIS splitting records with Script Component

  • Friday, March 01, 2013 5:00 PM
     
     
    Sign In to Vote
    0

    Guys,

    i have got below requirement which needs to be done in SSIS Script component. Can someone please help me to achieve this task

    Script component Input:

    RowID   Action  TestID

    1           B          234

    2           C         124

    2           C          654

    3           C         123

    3           NULL    345

    Here i need to split the records based on below scenarios

    Result Set 1: Action = B

    Result Set 2: Action = C but should have only one C for each Row ID ( in our example Row ID 3 will  be sent)

    Result Set 3: Action = C but should have more than one C for each Row ID ( Exception Scenario - Need to generate exception message) ( in our example Row ID 2 will be sent for error result set)

    Yes, I thk this should can be achieved in Script Component with 3 different output(Asynchronous).

    Can someone help me to achieve this easily in script component or any other easy ways.

    Note the inputs are from flat file source

    Balamurugan



     

All Replies

  • Friday, March 01, 2013 5:18 PM
    Moderator
     
     
    Sign In to Vote
    0
    Should the first C go to Set 2 and the next C's to Set 3?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Friday, March 01, 2013 5:26 PM
     
     
    Sign In to Vote
    0

    No, if the row id has more than 1 C (i,e multiple records of same row id and  same C) then it should go for set 3 (i.e exception row id is 2) 

    if the row id has only one C (i.e only one record with Action =C) then it should go for set 2 .

    Thanks in Advance


    Balamurugan


     
  • Friday, March 01, 2013 5:47 PM
    Moderator
     
     
    Sign In to Vote
    0

    It is possible with a Script Component, but you could also multicast the rows and then aggregate one flow to count the C's Then merge join both flows and add a conditional split after it.

    How large is your data set?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Friday, March 01, 2013 6:57 PM
     
     
    Sign In to Vote
    0

    Hi Bala,

    It is possible with a Script Component, but you could also multicast the rows and then aggregate one flow to count the C's Then merge join both flows and add a conditional split after it.

    How large is your data set?

    I also agree with SSIS Joost's idea of using in built transformations rather doing scripting. Can you please share your findings with this approach?

    Thanks, hsbal

     
  • Saturday, March 02, 2013 10:39 AM
     
     Answered
    Sign In to Vote
    0

    It might be length of 10 Lakh Records for each batch which runs every day. Thanks

    Balamurugan