We have developed a ETL project for datawarehousing. Our workflow goes as Replication - staging then Staging-DW. Now we have to show/Capture/Compare data processed verses data not processed . So we can analyse those data which are not processed and then find the root cause why it has not processed. We want to implement this in SSIS and I am little beginner in SSIS. Can some one give me steps how to implement this. In short I want to see processed data and unprocessed data (due to logic or exception) and before and after rowcount .
There is no short answer to provide that level of detail on how you would do this in SSIS. A short answer would be to learn SSIS and then focus on the following items for what you are wanting to do via data flow task (more than likely): - Row count Transormation will let you get the row count into a variable. You would then need to store that value in a file or write it to a table to persist. - Most of the data load task provide an option to send the records that error to another output stream. You can take that output stream to a row count transformation, to get how many rows erred, and then send that data to another destination to store it.