We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.
More often column data type or size validation fails the bulk insert process from Staging table to the Production table.
I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.
Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.
Example Error Message: Column PurchaseDate has invalid data
How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?