Solved

Why is data from Excel source converting to NULL?

Posted on 2010-09-10
2
585 Views
Last Modified: 2012-05-10
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
Comment
Question by:irb56
2 Comments
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33646426
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
 

Author Closing Comment

by:irb56
ID: 33647088
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now