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 10:55 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
I'm using VS2010 BIDS, importing from Excel 97-2003 .xls worksheet. I've got the following config:
Excel Source -> Conversion Split Transform -> OLE DB destination.
As long as I exclude the column in question the package runs and all columns import. However, if I enable the mapping for the column in question the excel source chokes on it.

The column in the source document contains letters, numbers, hash marks (#), and dollar signs($) etc. So, I have the destination column set to nvarchar(max) and have also tried nvarchar(255) up to nvarchar(800) but this column just won't import.

Within excel I have tried setting the column in question from General to Text format with no improvement. Because excel source chokes on this particular column I have found that adding a data conversion transform and doing a redirect on failure after the excel source transform to be useless.

Can you provide suggestions or explanation for what could be going on with this excel source column that makes ssis excel source transform choke on it???

Thanks in advance
Post #1453679
Posted Thursday, May 16, 2013 11:10 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:15 PM
Points: 22,491, Visits: 30,181
Chokes is a pretty vague description of the error. How about sharing the actual error you are getting, it may prove helpful.



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 #1453693
Posted Thursday, May 16, 2013 11:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 893, Visits: 6,882
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.



And then again, I might be wrong ...
David Webb
Post #1453695
Posted Thursday, May 16, 2013 11:45 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
No, it isn't destination related. The excel source just shuts the ssis operation down ie. won't bring over anything if I activate that particular column in the mapping of Excel Source transform.

If I enable the mapping for this column in the Excel source and subsequently on the OLE DB destination, then it fails at the source. If I disable the mapping on each end then all the other columns flow.

Nothing happens downstream of the Excel Source the moment I activate that one column.

That is why within excel I have been trying various column formats: custom, general, text. Here's a sample of what one may find in that column within Excel:
Backflow - Plumbing, 2.5"inch > Penthouse Mechanical Room
Post #1453710
Posted Thursday, May 16, 2013 11:52 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
for what it's worth, advanced editor for the excel source has the datatype property for this column set to Unicode string [DT_WSTR], 255. The destination is varchar(max).
However, as I say, if this column is enabled, the package fails at source.
Post #1453714
Posted Thursday, May 16, 2013 11:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 893, Visits: 6,882
So, in the excel source, in the advanced editor, it probably shows a mismatch between the actual column size (which isn't shown anywhere), the external column size and the output column size. Truncation will occur, and the task will stop, when it tries to move the external column to the output column. If you right click on the task and bring up the advanced editor, you can change the column lengths for those 2 entries and that may take care of the problem.

If you can run the package in debug mode, you'll probably get better error messages.




And then again, I might be wrong ...
David Webb
Post #1453715
Posted Thursday, May 16, 2013 11:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 893, Visits: 6,882
Is 255 enough? Bumping it up couldn't hurt. Does it match the output column datatype?



And then again, I might be wrong ...
David Webb
Post #1453718
Posted Thursday, May 16, 2013 12:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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

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 #1453722
Posted Thursday, May 16, 2013 12:15 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
thanks for hanging in with me.
First, correction: the destination column is set to nvarchar so it too is Unicode.

Next, I went to excel source advanced editor and attempted to edit the external metadata for that column from Unicode string [DT_WSTR] 255 to 800. But, external metadata just reverts back to 255 when I close the dialogue box. I upped the metadata for Excel source column's output to 800 and while it 'sticks' the package still fails at source.

I have been in scenarios where the Excel source transform passes the data through and then a column fails downs stream, and for this scenario I use data conversion transforms. But I am not getting past the ingestation stage even.
Post #1453724
Posted Thursday, May 16, 2013 12:15 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 I'll take a look.
Post #1453726
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse