no matter how large the destination column eg. nvarchar(max), excel source choke on column

  • 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:crying:

  • Chokes is a pretty vague description of the error. How about sharing the actual error you are getting, it may prove helpful.

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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.

  • opc.three I'll take a look.

  • 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.

  • 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

  • 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.

  • 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

  • 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.

Viewing 15 posts - 1 through 15 (of 51 total)

You must be logged in to reply to this topic. Login to reply