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 ««12345»»»

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

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:00 PM
Points: 680, Visits: 1,598
that article really speaks to my problem!
Where do I go to set the TypeGuessRows registry setting to a higher number? Where is the registry?

I implemented the first solution in the meantime to see if any improvement...but no, so solution 2 will hopefully be it!

Please tell me how to get to the TGR registry setting...thanks in advance.
Post #1453732
Posted Thursday, May 16, 2013 12:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
opc.three (5/16/2013)
The whole post is a good read, but section "The solution part III" pertains to your issue specifically:

http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/what-s-the-deal-with


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1453740
Posted Thursday, May 16, 2013 12:47 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:00 PM
Points: 680, Visits: 1,598
However, some data is flowing now:
Perhaps solution one was the correct solution....and now I have a data length problem?
Error messages:
[Excel Source [14]] Error: There was an error with Excel Source.Outputs[Excel Source Output].Columns[Asset Description] on Excel Source.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[Excel Source [14]] Error: The "Excel Source.Outputs[Excel Source Output].Columns[Asset Description]" failed because truncation occurred, and the truncation row disposition on "Excel Source.Outputs[Excel Source Output].Columns[Asset Description]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Excel Source returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Post #1453741
Posted Thursday, May 16, 2013 12:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
Check the outputs on your Excel Source Component. Hint: Solution III in the article I posted talks about it, as did David Webb earlier on this thread.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1453745
Posted Thursday, May 16, 2013 12:57 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:00 PM
Points: 680, Visits: 1,598
can you please tell me where I find the registry settings?.....even if that is solution 2? (because this problem is bound to crop up later or another excel source I will deal with in the future)

As far as problem that started the post, I am looking at whole article and rereading David's post for HINT.
Post #1453746
Posted Thursday, May 16, 2013 1:10 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:00 PM
Points: 680, Visits: 1,598
the answer to my registry question is:
To change the value of TypeGuessRows, use these steps: 1.On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
2.Open the following key in the Registry editor:


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Note For 64-bit systems, the corresponding key is as follows:


HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel <---except this should be: HKEY_CLASSES_ROOT/wow6432Node/Microsoft/Jet/4.0/engines/

3.Double-click TypeGuessRows.
4.In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
5.Click OK, and then exit the Registry Editor.
Post #1453749
Posted Thursday, May 16, 2013 1:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
You don't need to modify the registry, in fact I would discourage it so let's see if we can avoid it. If you modify the outputs for the Excel Source you should be able to overcome the issue.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1453750
Posted Thursday, May 16, 2013 1:31 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:00 PM
Points: 680, Visits: 1,598
David Webb-CDS (5/16/2013)
Are you sure it's choking on the destination? In the advanced editor, you might want to check both the external column and the output column on the excel spreadsheet and the external column on the destination. These default to 50, IIRC, and may need to be altered if the column is larger.


OK. I've gone to the excel source and sorted it in descending order so that the top row contains the following for the offending column. (BTW this is happening for two columns actually, but I've got them both unmapped, and am tackling them separately.
Column: "Asset description" column's first record looks like this:
shop air compressor - Speedaire State of Washington pressure vessel ID = 33501-03W

shop air compressor - Speedaire State of TX pressure vessel ID = 38801-03W

I've saved this excel file and headed over to Excel Sources Advanced Editor.

First of all, I can't change the metadata for external columns on Excel source. They revert. So, I don't understand that piece of advice. Next, I do not know what '50, IIRC' is referring to. My defaults are Unicode string [DT_WSTR] 255. I am able to change only the output of Excel Source and Inputs of subsequent transforms. But data isn't leaving Excel source....
Post #1453761
Posted Thursday, May 16, 2013 1:36 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:00 PM
Points: 680, Visits: 1,598



shop air compressor - Speedaire State of Washington pressure vessel ID = 33501-03W





looks like browser is converting the characters in my excel from HTML to human readable code. No matter what I do, I can't show you exactly what's in my excel source, but it's not pretty. It's html gobbelty goop

the asset description is actually populated with a bunch of hash marks, ampersands, and semi colons, all of which disappear into nice html when I click save.....
Post #1453763
Posted Thursday, May 16, 2013 1:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
Post a sample of your Excel file, attached to this thread.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1453766
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse