locked
Redirect Rows in SSIS RRS feed

  • Question

  • Hi All,

    I have to redirect rows which is from OLEDB source  , If there is more than one NumberID with same Number it should redirect all the NumberID's which has same NumberID to  Fail  destination .(ex- Not only duplicate, all ids which are more than one)

    Can i do this in ssis ?,or i have to use the script component ?

    i tryed below link but its giving me only Duplicate rows .

    http://microsoft-ssis.blogspot.com/2011/12/redirect-duplicate-rows.html

    Thanks,

    Prakash



    • Edited by prakash999 Thursday, August 23, 2012 5:38 PM
    Thursday, August 23, 2012 5:16 PM

Answers

  • You can only use that example for redirecting the duplicates (the second, third, etc row with the same values). If you want to redirect all rows (including the first row) you need a group by in the source query or a aggregate transformation is ssis (could be slow because it's a blocking component).

    You could try something like this in the source:

    SELECT			[myTable].NumberID 
    ,				[myTable].column2
    ,				[myTable].column3
    ,				[myTable].column4
    ,				aggregatedVersion.Count
    FROM            [myTable]
    INNER JOIN (
    			SELECT			NumberID
    			,				COUNT(*) AS Count
    			FROM			[myTable]
    			GROUP BY		NumberID
    			) as aggregatedVersion
    			on [myTable].NumberID = aggregatedVersion.NumberID
    Now you have an extra column in your data flow which you can use to redirect (conditional split)



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



    • Edited by SSISJoostMVP Thursday, August 23, 2012 6:46 PM
    • Marked as answer by prakash999 Friday, August 24, 2012 4:21 PM
    Thursday, August 23, 2012 6:44 PM

All replies

  • You would do need to use the Script Component set as a transformation. Make sure you sort the input data by NumberID.

    By the way Redirect Duplicate Rows was demonstrated very well here by SQLJoost http://microsoft-ssis.blogspot.ca/2011/12/redirect-duplicate-rows.html


    Arthur My Blog

    • Proposed as answer by DotNetMonster Thursday, August 23, 2012 6:57 PM
    Thursday, August 23, 2012 6:13 PM
  • HI Prakash,

    you can use the conditional split task and write two conditions 1) will redirect to a destination which do not have more than one row

    2) will redirect the rows which have more than one row.

    Thursday, August 23, 2012 6:34 PM
  • You can only use that example for redirecting the duplicates (the second, third, etc row with the same values). If you want to redirect all rows (including the first row) you need a group by in the source query or a aggregate transformation is ssis (could be slow because it's a blocking component).

    You could try something like this in the source:

    SELECT			[myTable].NumberID 
    ,				[myTable].column2
    ,				[myTable].column3
    ,				[myTable].column4
    ,				aggregatedVersion.Count
    FROM            [myTable]
    INNER JOIN (
    			SELECT			NumberID
    			,				COUNT(*) AS Count
    			FROM			[myTable]
    			GROUP BY		NumberID
    			) as aggregatedVersion
    			on [myTable].NumberID = aggregatedVersion.NumberID
    Now you have an extra column in your data flow which you can use to redirect (conditional split)



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



    • Edited by SSISJoostMVP Thursday, August 23, 2012 6:46 PM
    • Marked as answer by prakash999 Friday, August 24, 2012 4:21 PM
    Thursday, August 23, 2012 6:44 PM
  • Thank You ,
    Friday, August 24, 2012 6:14 PM
  • I am getting Only 4 Colums (rowID,MemID,Ind,Startdate,EndDate )

    Friday, August 24, 2012 6:16 PM
  • I am getting Only 4 Colums (rowID,MemID,Ind,Startdate,EndDate )


    goto to columns pane in source component and check the new column.

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

    Friday, August 24, 2012 6:21 PM