SSIS imports wrong numbers of data from excel
I am currently creating an SSIS package which imports data from excel to sql server. My problem now is I have 4998 rows on my excel source and everytime I try to run my SSIS package, it imports 5,010 data. I don't know where did it get the excess data. how can I fix this? can anyone help me please. Thanks!
Excel does this sometimes. It keeps track of the "data range", which sometimes extends beyond the actual data that exists. This "data range" is what SSIS will import.
If you look at your import table, you should be seeing 12 blank rows. Unless you've imported the header row, if any, as a row. That's another possibility: does your Excel sheet have multiple header rows (many do)?
It is possible to apply some kind of SQL to Excel datasources, but I avoid it because I don't trust Excel's handling of data. The solution is probably to identify a key column, which should always have a non-NULL, non-blank value of a certain type (e.g. a number or a date) in every "real" data row. Then delete rows that break this rule from your import table.
Excel importing is no fun!