Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Why is data from Excel source converting to NULL?

Hi,

I have an Excel data source with a column named policy_ref which contains data in a mixture of formats. Mostly the format of policy_ref is along the lines of ABC/123456. It so happens that the policy_ref values at the top of the Excel sheet contain refs in the format 123456, which Excel has automatically detected as numeric. I want to import this Excel sheet into a SQL table using a SSIS data flow task, converting policy_ref into varchar(255). To achieve this I have placed a Data Conversion transformation step in between the Excel source and OLE DB destination. The meta data of the Excel source output has set the data type of policy_ref to DT_R8 (a float type) and my Data Conversion creates a copy of this column to a DT_STR (varchar), which matches my OLE DB destination. After the package has executed I find that all but 53 records on my OLE DB destination table have a policy_ref value of NULL despite the facts that no blanks or NULLs exist in the Excel source for policy_ref.

Can anyone advise why this behaviour is occurring and how to manage it properly within SSIS? I realise that I could explicitly format the column in Excel, which may do the trick, but this is not really the solution I'm looking for because I want to automate the SSIS package and need to cater for users other than me updating the Excel source in ways that might remove a text format from the policy_ref column. If formatting the Excel spreadsheet first is the only way then so be it but I'd appreciate expert input on this before reaching such a conclusion.

Many thanks in advance.
0
irb56
Asked:
irb56
1 Solution
 
vdr1620Commented:
Excel automatically tries to guess the data type based on the first few rows.. In SSIS you will need to change the Excel Connection Manager properties

click on conn manager ---> Press F4 --> in the Connection String Change the value of IMEX from 0 to 1..that will stop Excel from guessing the data type
0
 
irb56Author Commented:
Thanks very much. The Excel connection string didn't have an IMEX option after changing it to the following, the issue of NULL values in the policy_ref column was resolved.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\policies.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

Great solution, much appreciated! :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now