Post reply

Excel Error

  • November 8, 2011 at 5:52 am

    #246347

    Hi,

    I have a Excel Spread sheet which has a column name JURISDICTION.

    It has Values like 1275,1276,1278 and some rows contains character "Other"..

    when i'm transforming this to Sql Table which has a column name "JurisID" ( Data type -Char(4))...

    the numeric values are inserting but the row which has "Other" values is not inserting..it gives error like below..

    [Derived Column [20]] Error: The "component "Derived Column" (20)" failed because truncation occurred, and the truncation row disposition on "output column "JurisID" (286)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    [Excel Source [1]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (20) failed with error code 0xC020902A while processing input "Derived Column Input" (21). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209017. 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.

    Thanks for any help or information

    Thanks,
    Charmer

  • November 8, 2011 at 6:47 am

    #1404484

    How many characters has the word Other?

    How many characters has your destination column?

    Et voila, there is the solution 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 8, 2011 at 7:52 am

    #1404565

    Other has 5

    and Destination allows 4

    Thanks,
    Charmer

  • November 10, 2011 at 8:12 am

    #1405793

    well, there you go. truncation.

  • November 11, 2011 at 2:18 am

    #1406126

    in case truncation....what should i do?

    Thanks,
    Charmer

  • November 11, 2011 at 3:43 am

    #1406156

    Charmer (11/11/2011)

    in case truncation....what should i do?

    Hello,

    you must decide what is the answer to this question. You receive a very clear message: "Other" cannot be put into a CHAR(4) column. Now your business rules should state what to do with this value "Other". I can imagine now two options:

    - Change your table design and alter this CHAR(4) column to CHAR(5).

    - If your business rules say you should substitute "Other" by "xyzt" change mappings in SSIS to reflect this substitution.

    Regards,

    Francesc

  • November 11, 2011 at 5:46 am

    #1406194

    i understand the problem.....In some way the "other" is considered as null itself by the derived column.....why it does not take up to 4 char like "Othe" and insert into destination..?

    why is it not considering up to 4 characters in the derived column?

    Thanks,
    Charmer

  • November 11, 2011 at 8:28 am

    #1406325

    Hello,

    http://technet.microsoft.com/en-us/library/ms141679(SQL.110).aspx

    maybe this link can help you to change the truncation behavior.

    Francesc

  • November 11, 2011 at 12:01 pm

    #1406544

    Charmer (11/11/2011)

    i understand the problem.....In some way the "other" is considered as null itself by the derived column.....why it does not take up to 4 char like "Othe" and insert into destination..?

    why is it not considering up to 4 characters in the derived column?

    Because you specified (or rather, the default behaviour of SSIS specifies) that truncation gives an error. You can change this at the error settings for the specific component.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 11, 2011 at 12:23 pm

    #1406549

    or prevent the error from occurring at all by only accepting valid entries into the dataflow and/or specific component.

    a text entry is not at all the same as a null value. while spelling null costs 4 characters, that does not make it a 4 character text entry.

    Here, we will not accept nulls in our dw so my ETL must handle those. If they are not handled my package will fail. Clear indication that something is wrong and I must fix it. I have set db fields to not accept nulls.

    Where to handle the issue is a decision all in itself.

    When I use a lookup and expect the case of no-match, I set the error handling to ignore that kind of failure. Someone else might need to divert no-matches to a file or other output. Maybe you don't care about truncation and can ignore that kind of error, but if you do that in your ETL, the destination still may not accept it.

    The point is, the intricacies of your packages are dictated by the environment in which you serve. Maybe you are to take only the first 4 characters or may divert any data greater than 4 characters to another flow?

    That's not something we can answer for you.

Viewing 10 posts - 1 through 9 (of 9 total)

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