Solved

Why is data from Excel source converting to NULL?

Posted on 2010-09-10
2
583 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

22 Experts available now in Live!

Get 1:1 Help Now