April 2, 2015 at 3:40 pm
Hi Guys,
I am having a problem, client provide a .xlsx file and one file has below sample data
ABC (E.G)
123487
894651
ABC-879655
ED-78545
When I right click on excel source and go to Show advance editor/input and output properties, the column has data type "Double Precision Float" if i keep it same package runs fine however I am getting below data in my destination SQL Table
ABC
123487
894651
NULL
NULL
fyi, I am using below Variables to execute my SSIS Package
@ExcelP1 = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
@ExcelP2 = ;Extended Properties="Excel 12.0 XML;IMEX=1;HDR=YES";
@FileName = "Where my source file is located"
Then I am using below EXPRESSION in my Excel Connection manager string
Connection String = @ExcelP1+@FileName+@ExcelP2
Still I have a problem. Please advise. Its urgent.
Thank You.
April 3, 2015 at 6:33 am
Set the TypeGuessRows registry setting for the ACE OLE DB provider to 0. It will scan more rows to determine the data type.
Also, make 100% sure IMEX=1 is included in the final connection string.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2015 at 9:37 am
Set the TypeGuessRows registry setting for the ACE OLE DB provider to 0. It will scan more rows to determine the data type.
Also, make 100% sure IMEX=1 is included in the final connection string.
I did change TypeGuessRows registry setting for Jet default 8 to 0, still having a problem. The Quick question could you please tell me is this right place where I am updating TypeGuessRows to 0?
HKEY_LOCAL_MACHINE ==> SOFTWARE ==> WOW6432NODE ==> MICROSOFT ==> JET ==> EXCEL==> TYPEGUESSROWS
Thank You.
April 3, 2015 at 11:26 am
tooba111 (4/3/2015)
Set the TypeGuessRows registry setting for the ACE OLE DB provider to 0. It will scan more rows to determine the data type.Also, make 100% sure IMEX=1 is included in the final connection string.
I did change TypeGuessRows registry setting for Jet default 8 to 0, still having a problem. The Quick question could you please tell me is this right place where I am updating TypeGuessRows to 0?
HKEY_LOCAL_MACHINE ==> SOFTWARE ==> WOW6432NODE ==> MICROSOFT ==> JET ==> EXCEL==> TYPEGUESSROWS
Thank You.
No, that's for the older JET provider.
You need to set it for the ACE OLE DB provider.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2015 at 12:15 pm
Thank you for prompt reply, Could you please help me where I can find ACE OLE DB provider?
One more quick question, do I have to change on my local computer and server as well, right?
Thank You.
April 4, 2015 at 4:28 am
tooba111 (4/3/2015)
Thank you for prompt reply, Could you please help me where I can find ACE OLE DB provider?One more quick question, do I have to change on my local computer and server as well, right?
Thank You.
I don't know the path by hard, but you can search the registry for TypeGuessRows. It should be in the Office path.
You need to do this locally and on the server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 4, 2015 at 10:17 am
Does this path look good to you?
HKEY_LOCAL_MACHINE==>SOFTWARE==>WOW6432NODE==>MICROSFT==>OFFICE=>12.0==>ACCESS CONNECTIVITY ENGINE ==>ENGINES==>EXCEL==>TYPEGUESSROWS
Thank You.
April 7, 2015 at 12:36 am
Yes, that seems about right.
If you run the package in 64-bit, you need to set the registry setting in the normal path as well (without the wow6432node).
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