Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have done some tests in flat file source trying to see if it read the file in order of the number of lines and all of them the number of the line read was inserted in SQL Server equally.
I was wondering if I can be sure that it will always read and insert in this order.
You can use Row Count tasks in your data flow to determine how many rows pass through which part of your solution. This can be helpful if you put it immediately after a source and before a destination... assuming that you are actually performing logic that could eliminate rows.
People will often record these values to SSIS variables and then write this information to a log table of rows read and written per package per execution. Of course, I've seen very few take the time to validate or create tests on all this custom logging work which often times is merely a check on Microsoft's tools. Take the time to consider what new issues you're introducing into your solution, points of failure and dependencies, and the benefits you're creating.
There are good designs that reside in the DB that can easily answer such questions and more...
[AuditInsertDate] DATETIME NOT NULL DEFAULT GETDATE()
Covers additional use cases, provides timing information, and tracks your insert counts (in most load scenarios). It's also a lot easier to implement and maintain than custom SSIS logging.
