1

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.

2
  • Hi, First of all, welcome to the site. We will need a bit more to work with here. What table are you inserting into. What was the file. How do you determine the line number etc. There is not necessarily an order in a table, there might be an order by in your query
    – Tom V
    Jan 5, 2016 at 19:25
  • You ever use a Sort Transformation? msdn.microsoft.com/en-us/library/ms140182.aspx I believe it will give you the guarantee you are looking for depending on your input set.
    – mskinner
    Jan 5, 2016 at 19:44

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.