I have a package which imports data from excel to sql server.

  1. Excel file has a column with first x rows are numeric and the remaining values are varchar
  2. Derived column task to convert to string
  3. Destination table is varchar

Here's where I am scratching my head.. When I look at the advanced properties for the excel source task, the in/out datatype is picked up as double precision float. The entire data set successfully loads into my destination table however all the string values are populated as NULL, instead of erroring out at the excel source task.

I can ultimately solve this issue by formatting the column in excel as text but my question is, why the package does not error out? Please let me know if I'm missing any important details.

  • What's the errorrowdisposition and truncationrowdisposition on the output column? – Tom V - try topanswers.xyz Aug 16 '17 at 8:13
  • Keep in mind that when importing an Excel file into SSIS, the Jet driver only "peeks" at the top 8 rows (according to this article) to auto-determine what the best datatype for that column is. – Oreo Aug 16 '17 at 9:08
  • A fix, from the above article: "To fix this issue, we can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window." – Oreo Aug 16 '17 at 9:37
  • @TomV Checked this yesterday while troubleshooting to ensure they are both set to RD_FailComponent. – wonderstruck80 Aug 16 '17 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.