SSIS load validation

Question

I'm importing data from a text file using Bulk Insert in the script component in SSIS package.

Package Ran successfully and data imported into SQL

Now how do I validate the completeness of the data? 1. I can get the row count from source and destination and compare. but my manager wants to know how we can verify all the data has come a cross as it is without any issues.

If we are comparing 2 tables then probably a joining them on all fields and see anything missing out.

I’m not sure how to compare a text file and a sql table. One way I could is write code to ready the file line by line and query the database for that record and compare each and every field. We have millions of records so this is not going to be a simple task.

Any other way to validate all of the data ??

Any suggestions would be much appreciated

Thanks Ned

Answer 1

Well you could take the same file and do a look-up to the SQL source and if any of the columns don't match move to a row count.

Here's a generic example of how you can do this.