Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 863
  • Last Modified:

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.
0
quasar_ee
Asked:
quasar_ee
  • 3
2 Solutions
 
Jim HornMicrosoft 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:
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 HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now