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
KoldCoffee
KoldCoffee
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5359 Visits: 1905
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 advanceCrying
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117870 Visits: 39112
Chokes is a pretty vague description of the error. How about sharing the actual error you are getting, it may prove helpful.

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)
David Webb-CDS
David Webb-CDS
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5130 Visits: 8586
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
KoldCoffee
KoldCoffee
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5359 Visits: 1905
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
KoldCoffee
KoldCoffee
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5359 Visits: 1905
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.
David Webb-CDS
David Webb-CDS
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5130 Visits: 8586
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
David Webb-CDS
David Webb-CDS
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5130 Visits: 8586
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
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51986 Visits: 14413
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
KoldCoffee
KoldCoffee
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5359 Visits: 1905
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.
KoldCoffee
KoldCoffee
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5359 Visits: 1905
opc.three I'll take a look.
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