How to filter data in SSIS?
I have a flat file that I import into SQL Server. The sample data is as follows: vendor_num vendor_name quality serial_no name includes 1234 Steakhouse STANDARD 1 Filet Y 1234 Steakhouse STANDARD 1 Chicken N 1234 Steakhouse STANDARD 1 Shrimp M 1234 Steakhouse STANDARD 2 Filet 1234 Steakhouse STANDARD 2 Chicken 1234 Steakhouse STANDARD 2 Shrimp 1235 Eddies STANDARD 2 Filet Y 1235 Eddies STANDARD 2 Chicken N 1235 Eddies STANDARD 2 Shrimp M 1236 Jenn's Pizza STANDARD 2 Filet Y 1236 Jenn's Pizza STANDARD 2 Chicken N 1236 Jenn's Pizza STANDARD 2 Shrimp M how do I filter out duplicates which have no values from the above data before insert into the sql server table? eg: for the above input I need to only enter data for 1234 Steakhouse and serial_no = 1 for which there is includes data. is there an easy way in SSIS to accomplish this?
February 8th, 2011 9:44am
You maybe able to work out a proper constraint on the destination table to force the data input.Sevengiants.com
February 8th, 2011 9:52am
You need to use conditional split based on the includes column. use the expression [includes] == "Y". Then use the output from this as the input for ur oledb destination.My Blog | Ask Me
February 8th, 2011 9:59am
data in the includes column could be anything from Y , N or M or empty. Also if the data has empty values and there are no other codes for, I would need this data. eg: in the below sample data the code 1237 has three values with serial_no = 2 , eventhoug the includes column does not have any values I would need the below values because there are not other values for vendor_num - 1237 1237 Jenn's Pizza STANDARD 2 Filet 1237 Jenn's Pizza STANDARD 2 Chicken 1237 Jenn's Pizza STANDARD 2 Shrimp
February 8th, 2011 2:07pm
In that case you would have to go for script component.My Blog | Ask Me
February 8th, 2011 2:11pm
Read Eliminating Duplicate Primary Keys in SSIS. You may need to use an asynchronous script, since your decision as to what rows to push through seems to depend on other rows' contents. Talk to me now on
February 8th, 2011 2:23pm