Digits converted to null

Hot Topic

report server compatability issues
And/or filter field not enabled in the group filter tab
How to dynamically change width?
Bug in expression evaluator using NULLIF and RAND()
Creating CLR stored procedures, anyone have a good tutorial?
Strange issue with Precedence Constraints and Set Values
Web Services Task Editor: The input Web Services Description Language (WSDL) file is not valid
Creating a job for SQL 2000 using SQLDMO in c#
None of the Management Tools Installed...
Reusing a Chached Lookup component

SQL Server

Report Model in VM environment
Error rendering sub-reports
Execute Integration Package from a C# Solution
Send Email Task - Error Reporting
Transfer SQL Server Objects Task doesn't work with schemas
Replication between SQL CE 2.0 and SQL Server 2005 not working
How do I shrink the log of a DB used in trans replication?
VBA IsError function in MDX with SQL Server 2005
Unable to retrive data in Sql 2005
Error 18452 (not associated with a trusted sql server connection)

Digits converted to null

Hi,


Im loading data from excel source into a table with all the columns as varchar, I found out that rows from excel  with digit value are transformed to Null values into the destination table.

One workaround was to add single quote at the beginning of the digits from the excel file. Is there a way in the SSIS to do the transformation instead ofmanually updating the excel file

any help...tnx..

Answer this question

Digits converted to null

  • u_r_twisted

    You need to specify Import Mode by adding IMEX=1 to the connection string, in the Extended Properties argument along with the Excel version and HDR name/value pairs.

    Please note that we have done our best to document this and other known issues in the topics for the Excel Source and the Excel Destination in BOL. This content has been further augmented for the upcoming Web refresh of BOL.

    -Doug

  • lizzy64

    This type of problem was also applicable to DTS so this article should probably still apply Excel Inserts Null Values (http://www.sqldts.com/default.aspx 254) Allan "DouglasL@discussions.microsoft.com" wrote in message news:3b274106-b020-42bb-95cf-a1aeb554ea98@discussions.microsoft.com: > You need to specify Import Mode by adding IMEX=1 to the connection > string, in the Extended Properties argument along with the Excel version > and HDR name/value pairs. > > Please note that we have done our best to document this and other known > issues in the topics for the Excel Source and the Excel Destination in > BOL. This content has been further augmented for the upcoming Web > refresh of BOL. > > -Doug

  • Big Andy 78

    Not that I know of and I've spent some time looking.  Reading data from Excel is tricky.  For example, the data type of the column can change from row-to-row, and Excel can store data that doesn't match the defined data type.  These are pretty big challenges for an OLE DB provider trying to read it like a table.

    In several cases I've resorted to exporting my Excel source to a tab-delimited file for SSIS to read.  At least you can automate this instead of having to manually fix each Excel file.
  • Digits converted to null
Answer this question