ssisssis-2012
I am loading data from csv file to my sql table through SSIS. Is there any default limit specified on number of records to be read from csv file ?
While loading the csv file, my data flow component is processing only 5000 records, though it contains 5341 records,as shown in the image below.How can i fix this ?
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
This warning message displays when any time source column data type length will be more than then length of destination datatype. To resolve the warning message check here -
Best Answer
The source file shows only 5000 coming through from the first step.
This means all of the data in the file got imported and there was a bad delimiter making some of the data get merged in the import or you have error notification / truncation notification turned off and the data was truncated due to destination or transformation constraints.
You could also have configured data rows to skip on accident or there are actually only 5000 rows of data. I'd start by examining the data, then looking into the logs. Make sure SSIS logging is enabled and look to the logs for more information. I would also add a count step between each of the steps and store them into a table for tracking so I can verify the amount of rows coming through each way.