SSIS: validating data loaded from flat file into the table

What is the best way to make sure that number of rows in flat file match number of rows in the database?
Thanks.
quasar_eeAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Just discovered you can add a row count after the destination task, which only works for the errors, which means you could add a Row Count Transform between the two, assign to a variable, and test to make sure it is zero.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Add a Row Count transform right after the source to get the number of rows in the flat file.

>match number of rows in the database?
Assuming you mean the target table...
The easy way would be to use Task Factory's Upsert Task, which writes the inserted rows to a variable, then you could just compare the two variables.
Without that, afaik SSIS does not have functionality in its Destination Task to do a rowcount of inserts, so you'd probably have to do a SELECT COUNT(*) before and after the data pump.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.