Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

no matter how large the destination column eg. nvarchar(max), excel source choke on column Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 6:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 22,475, Visits: 30,157
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.



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)
Post #1453817
Posted Thursday, May 16, 2013 8:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy


  Post Attachments 
5194041e.jpg (136 views, 198.54 KB)
Package2.rar (2 views, 17.80 KB)
Post #1453829
Posted Thursday, May 16, 2013 9:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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.
Post #1453830
Posted Thursday, May 16, 2013 9:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1453832
Posted Friday, May 17, 2013 11:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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.
Post #1454107
Posted Saturday, May 18, 2013 7:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1454299
Posted Saturday, May 18, 2013 8:04 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209


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.
Post #1454302
Posted Saturday, May 18, 2013 9:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1454303
Posted Saturday, May 18, 2013 10:31 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 5:45 PM
Points: 519, Visits: 1,209
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.
Post #1454311
Posted Sunday, May 19, 2013 7:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1454325
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse