July 7, 2011 at 12:05 pm
Hi,
I am struggling with loading Excel files in SSIS. If a colums contains mixed alpha and numeric characters it gets changed to null. Here is my connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CDP\Paiq\Public\1_A_Traiter\interfaces\Chargement_INOC_IVANHOE_2010T4.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";
What is odd is that when I run only the data flow task, it seems to work fine (as I can see in the data viewer). But when I run the whole package, then the problem with the nulls happens.
Any help would be much appreciated.
Chris
July 11, 2011 at 3:12 am
Christian Allaire (7/7/2011)
Hi,I am struggling with loading Excel files in SSIS. If a colums contains mixed alpha and numeric characters it gets changed to null. Here is my connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CDP\Paiq\Public\1_A_Traiter\interfaces\Chargement_INOC_IVANHOE_2010T4.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";
What is odd is that when I run only the data flow task, it seems to work fine (as I can see in the data viewer). But when I run the whole package, then the problem with the nulls happens.
Try setting the registry setting TypeGuessRows to 0. Excel scans the first 8 rows to determine the data type. If two or more data types are found, IMEX=1 tells Excel to use the String data type. However, if your first 8 rows only contains strings, it will use strings and convert the numeric values (that come later on in the column) to NULL. Setting TypeGuessRows to 0 tells Excel to scan the whole column to determine the data type.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 3:23 am
The fact that the data shows up OK in the data viewer suggests to me that this is not a classic Excel-datatype issue, because it seems that the data is getting into the pipeline but not getting out. Are you able to identify exactly where in your process the problem occurs?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2011 at 3:37 am
Phil Parkin (7/11/2011)
The fact that the data shows up OK in the data viewer suggests to me that this is not a classic Excel-datatype issue, because it seems that the data is getting into the pipeline but not getting out. Are you able to identify exactly where in your process the problem occurs?
My experience teaches me that the preview and running the package are not exactly the same when working with Excel.
For example: preview works but running doesn't, because 64-bit mode is turned on. Probably because BIDS is 32-bit, so the preview is OK, but when running the package 64-bit is simulated...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 1:40 pm
Thanks for the reply.
The data is read by the data source and shows up in the data viewer only if I run the the Data Flow Task directly (Execute Task). However if I execute the package then the data viewer shows nulls.
I should also add that I am using Excel 2007 xslx files. I have read posts where people succeeded with the JET 4.0 Connector and Excel 8.0, but I have yet to find a single case on the web were a solution was found with ACE.OLEDB.12.0 and Excel 12.0.
Chris
July 11, 2011 at 2:10 pm
Christian Allaire (7/11/2011)
Thanks for the reply.The data is read by the data source and shows up in the data viewer only if I run the the Data Flow Task directly (Execute Task). However if I execute the package then the data viewer shows nulls.
I should also add that I am using Excel 2007 xslx files. I have read posts where people succeeded with the JET 4.0 Connector and Excel 8.0, but I have yet to find a single case on the web were a solution was found with ACE.OLEDB.12.0 and Excel 12.0.
Chris
Did you change the registry setting for the ACE provider?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 2:33 pm
Yes I did, but it had no effect. I put TypeGuessRows = 0.
At first did it on the Jet section of the registry, but I realized my mistake and put in the Office 12.0\Access Connectivity Engine\Engines\Excel.
Could this be related to a 64 bit vs 32 bit issue ? Or some bug with the xlsx format ?
Chris
July 11, 2011 at 11:30 pm
Just to make sure, is the project property Run64BitRunTime set to False?
Does the entire column show NULL values, or just some specific values?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply