SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with excel 2007 import using SSIS 2005


Problems with excel 2007 import using SSIS 2005

Author
Message
Palash Mitra
Palash Mitra
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 11
Hi,

I am trying to import data from excel 2007 into my sql server 2005 database using SSIS 2005. SSIS 2005 cannot read .xlsx files using excel data source. So I have used OLE DB data source alongwith Microsoft Office 12.0 Access Database Engine OLE DB provider.

This works out fine except for text fields which are getting truncated when they have more than 255 characters. These columns are comments to be included by the business and as such have the potential of exceeding the 255 character barrier. [Note that the table where the text column is targeted has ntext as datatype]. On doing some web searches I have come across suggestions to increase TypeGuessRows doing registry edit.
Ref: http://support.microsoft.com/kb/281517

But found that that only works when the source in SSIS dataflow is an excel datasource.

Any ideas?

Regards,
Palash.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27281 Visits: 13268
Setting TypeGuessRows will help if the JET provider is used, it doesn't really matter if it is the Excel source or a script task using OLE DB.
There is an equivalent setting for the ACE provider, you can find more details in the following thread:

http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/40f62ac7-7a14-44a7-9e38-115fde4f0e66


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Palash Mitra
Palash Mitra
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 11
Hi Koen,

Was missing just that! Thanks a ton!

Went to registry and updated TypeGuessRows to 0; only this time @HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel.

And the dataflow worked just fine. However, there could be a potential downside to this solution if the excel is too big.

Regards,
Palash.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27281 Visits: 13268
Palash Mitra (3/8/2011)
Hi Koen,

Was missing just that! Thanks a ton!

Went to registry and updated TypeGuessRows to 0; only this time @HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel.

And the dataflow worked just fine. However, there could be a potential downside to this solution if the excel is too big.

Regards,
Palash.


Great! Glad that it worked out.
The TypeGuessRows setting can indeed affect performance very badly.
But if your Excel file is that large, I would suggest to convert it to .csv and import it as a flat file. Much more efficient.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Tab Alleman
Tab Alleman
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 115
[quote]Palash Mitra (3/7/2011)
Hi,

On doing some web searches I have come across suggestions to increase TypeGuessRows doing registry edit.
Ref: http://support.microsoft.com/kb/281517


[quote]

That KB article says it only applies to SQL 2000 and 7.0. Can anyone confirm that this registry key is still used with 2005+?

(I am a developer who doesn't have machine-level access to our SQL Server, or I'd look myself)



Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27281 Visits: 13268
Tab Alleman (6/7/2013)
[quote]Palash Mitra (3/7/2011)
Hi,

On doing some web searches I have come across suggestions to increase TypeGuessRows doing registry edit.
Ref: http://support.microsoft.com/kb/281517


[quote]

That KB article says it only applies to SQL 2000 and 7.0. Can anyone confirm that this registry key is still used with 2005+?

(I am a developer who doesn't have machine-level access to our SQL Server, or I'd look myself)


It works for every version of the JET OLE DB provider (right until Excel 2003) and for every version of the ACE OLE DB provider (until Excel 2013 for the moment).
The version of SQL Server doesn't matter, it is the OLE DB provider that is giving issues.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search