Solved

Why is data from Excel source converting to NULL?

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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