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


no matter how large the destination column eg. nvarchar(max), excel source choke on column


no matter how large the destination column eg. nvarchar(max), excel source choke on column

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96657 Visits: 38988
opc.three (5/16/2013)
Weird. Link works for me, still.

Kold Koffee. We're volunteers so the lack of a response within a few minutes ir even hours just means we may have had to do something for $ before we could get back to your issue. I will have a look tonight or tomorrow morning at the file you posted.


Works from home too, just wouldn't work at work.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40582 Visits: 14413
So only changing the metadata on the Excel source with the Advanced Editor did not work for me this time. I had to go full boar and ended up altering the registry to get a working solution. I changed the value of registry entry TypeGuessRows from 8 to 0. On my x64 Windows 8 machine the registry entry is located in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.

The test package is attached along with the xls file. Here is the table definition too. For simplicity I only bothered to import the Asset Description column and no truncation occurred.

USE [YourDatabaseName]
GO

/****** Object: Table [dbo].[OLE DB Destination] Script Date: 5/16/2013 8:48:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OLE DB Destination](
[Asset Description] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO





__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
5194041e.jpg (159 views, 198.00 KB)
Package2.rar (6 views, 17.00 KB)
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4247 Visits: 1905
opc.three that is fantastic!!! I thought Lynn Petis trumped the topic.
Link works for me too.
I have not yet reproduced your success on my machine and it's the next thing I will do.
I hope I have admin rights to alter registry on work laptop. When I tried to change my registry earlier the value didn't stick and I saw your post about it not being recommended so didn't take an iron fist to it.
Will update later.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40582 Visits: 14413
Ok. Let us know how it goes.

As an aside, this is a discussion site, not just a Q&A site, so try to be patient with some tangents once in a while, because they happen. Some of the best info is tucked into those tangents. Know that we are all here to help each other and learn.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4247 Visits: 1905
opc.three.

This worked and it brings me a great deal of relief. Thank you.

I attempted to add the TypeGuessRows property to the excel source connection string hoping to make this a portable solution, like so:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\v-helkr\Documents\MOR ETL\360_PMEquipment.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;TYPEGUESSROWS=0\";

but alas it didn't work. Only changing in the registry AND restarting the machine before re-running the package worked.

To answer your aside:
When a topic on sqlservercentral flows the best, is when the those who join genuinely want to help you with the issue at hand. The most knowledgeable contributors here are usually not derailing the topic. The hope is that no one has time for that at least.

I appreciate the answer and the pertinent discussion. My gratitude to you and sqlservercentral!

PS Edit: thanks David.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40582 Visits: 14413
KoldCoffee (5/17/2013)
opc.three.

This worked and it brings me a great deal of relief. Thank you.

I attempted to add the TypeGuessRows property to the excel source connection string hoping to make this a portable solution, like so:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\v-helkr\Documents\MOR ETL\360_PMEquipment.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;TYPEGUESSROWS=0\";

but alas it didn't work.

It was worth a try but I would have been surprised if it worked. The connection string contains runtime options and TypeGuessRows is only relevant at design-time.

Only changing in the registry AND restarting the machine before re-running the package worked.

Which OS? Just curious and others who see this thread later may be interested. I am on x64 Windows 8 and not have to reboot.

To answer your aside:
When a topic on sqlservercentral flows the best, is when the those who join genuinely want to help you with the issue at hand. The most knowledgeable contributors here are usually not derailing the topic. The hope is that no one has time for that at least.

Eh, "best" is subjective, at best :-D The tangents are why I keep hanging around here and don't spend much time on sites like ask.sqlservercentral.com or SO. Those sites are strictly moderated the way you're describing and frankly I find it a bit off-putting and have seen it stifle the problem solving and learning process. Strict Q&A sites are great for getting quick answers to the easy stuff (like this question might have been a good candidate) but not so great at allowing us to get beneath the surface into the nitty-gritty details of a problem or platform and share what we have learned to solve interesting, difficult or uncommon problems, or even somewhat common problem in a creative way. At any rate, I am happy you got a path to a working solution and are able to move forward on your project.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4247 Visits: 1905


Which OS? Just curious and others who see this thread later may be interested. I am on x64 Windows 8 and not have to reboot.



Windows 8 Enterprise 64 bit.


This, easy stuff? Come on now....not really opc.three. It so happens you were there to answer the question. You got it right from the start. I might have busily engaged in editing the weird characters out of my source files entering a state of holiness by now if you hadn't helped me.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40582 Visits: 14413
To be fair "easy" is subjective too. I guess i just see this one as more about knowing which config options are there as opposed to having to design an application or write a complex query. Once you know how the Excel driver works it's easy to understand what's happening and it's quite a simple fix.

http://ask.sqlservercentral.com/questions/47200/integration-services-2005-excel-connection-manager.html/u]

[u]http://stackoverflow.com/questions/13452326/error-in-datatype-nvarchar-instead-of-ntext


Thanks for the dialogue by the way and for posting your OS version. I guess it depends on conditions we probably won't know about as to whether a reboot will be required.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4247 Visits: 1905
I looked at the first link (second not redirecting) and now I see why I go here. Same issue and same conclusion but:
1. I would not have recognized it as being the same as my problem. Only in hindsight I do.
2. The answer didn't say anything about what value to supply TypeGuessRows. The answer assumed that when you find that property it will be set to 8 by default, that the person posting the issue will deduce 8 rows and know to change it to 0....or 16.

On this forum, most of the time, people help you discover the nature of the problem when you yourself don't know. They really stick by you and understand what end outcome you need to achieve and help support you.

I think of my own child for whom the world is new, who comes to me with a problem and doesn't know what it is but needs to come to understand it before she can move forward with implementing a solution.

Great place: sqlservercentral.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40582 Visits: 14413
Those were two quick samples. There are tons relating to this issue, but I hear what your saying about not knowing how to ask a question when you don't know what you're up against. Not you, but sometimes people post a waffling narrative and wrap up their post with "any help is appreciated". Questions like that get edited by the moderators on SO, or categorically thrown out, whereas here they spark very diverse responses from folks looking at the problem through their own lens of experience. That's how problems get solved and people learn how to ask better questions.

A friend recently said to me that everyone has different perspectives on problems and how they approach solving those problems, and it is the vast array of experiences that we bring to a question.

When the response from the poster becomes "I don't need that information just focus on the question I asked" when in reality that's what was being done, we all lose something.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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