Redirect Rows in SSIS

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

Thank You ,

Answer 5

I am getting Only 4 Colums (rowID,MemID,Ind,Startdate,EndDate )

Answer 6

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