no matter how large the destination column eg. nvarchar(max), excel source choke on column
-
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.
-
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 -
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.
-
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 -
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.
-
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 😀 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 -
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.
-
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://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 -
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.
-
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 -
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.
Sorry, but even in a forum where each can add to the discussion with their lens of experience, there is something as a derailing contribution. Even here. There is enough breadth to discuss without going off topic ie. questioning the entire existence of the problem as legitimate to begin with.
Foundation: trust that the person posting the topic wishes to achieve a solution for *that* problem. The person does not want to examine his whole existence and the reason the ocean is blue and build a big arena and paint the ceiling red and then build a model ocean under it, just to see if that adds perspective to the problem. . . just because it is a discussion forum.
-
KoldCoffee (5/19/2013)
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.
Sorry, but even in a forum where each can add to the discussion with their lens of experience, there is something as a derailing contribution. Even here. There is enough breadth to discuss without going off topic ie. questioning the entire existence of the problem as legitimate to begin with.
Foundation: trust that the person posting the topic wishes to achieve a solution for *that* problem. The person does not want to examine his whole existence and the reason the ocean is blue and build a big arena and paint the ceiling red and then build a model ocean under it, just to see if that adds perspective to the problem. . . just because it is a discussion forum.
So, let me get this straight, if a person is asking for a solution using a cursor then that is all we should provide even if there is a better set-based solution for the problem. Sorry, but I really can't agree with that.
-
Ah yes Lynn, I was wanting to learn cursors back then, not defend my use of them. Cursors are bad RBAR for the most part, but they have their place, and thanks to David, I got the answer.
It's like having a bad thought. Sometimes, you just have them and you ask how to deal with them. Someone could say, you should never have bad thoughts....you must think positive....but that isn't helpful:-)
-
KoldCoffee (5/19/2013)
Ah yes Lynn, I was wanting to learn cursors back then, not defend my use of them. Cursors are bad RBAR for the most part, but they have their place, and thanks to David, I got the answer.It's like having a bad thought. Sometimes, you just have them and you ask how to deal with them. Someone could say, you should never have bad thoughts....you must think positive....but that isn't helpful:-)
Sorry, but I really don't see a response from you on this part of my statement:
So, let me get this straight, if a person is asking for a solution using a cursor then that is all we should provide even if there is a better set-based solution for the problem.
Based on everything you have said my take is yes, just answer the question and don't try to help the person learn a better way of doing things. I'm I right or wrong?
Also, I have never questioned on their problem just on their approach to solving it. That is completely different and it is meant try and help the OP to look at a problem from a different perspective, not question their existence.
-
In that case I was trying to learn cursors, and there was no better way. I even prefaced and said something like, I don't want to be talked out of a cursor because I am aware of pros and cons...in the hopes of avoiding getting derailed.
Otherwise, I always agree with platitudes.
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply