I'm trying to build an SSIS package. The process flow of the package would be:
1.Read the data from table A.
2.Validate if the data in table A exists in table B. If there are any records that contains invalid data in table A, Abort the package, error and email notify to a distribution group.
3.Upon validation, hold for union with the set of records in Step 2.
1.Read the data from a CSV file.
2.Validate each field in file. If there are any invalid records, publish the error records to the same location and filename with "ERROR_" appended to the begining.
3.Upon validation of records, hold for union with Step 1 records.
Union records from Step 1 and Step 2 and write them into a table.
My question is, how do I group only the invalid records and publish to an error file in Step 2.