sql serverssis
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've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.
Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.
With only that change, the package will execute successfully
Also, an excellent answer from SO on the same issue
You can follow either of the below approach :
Refer to this excellent series : Stairway to Integration Services for more details.
Best Answer
You can use
Row Counttasks 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...
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.