Solved

Why is data from Excel source converting to NULL?

Posted on 2010-09-10
2
590 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 78
How to keep a record with the highest value 3 59
What Is an Error? 2 59
efficient backup report for SQL Server 13 81
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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