Invalid character value for cast specification in OLEDB command

Bala Narasimha Challa 466 Reputation points
2021-08-17T13:24:30.597+00:00

Hi Team,

Am using bellow statement in oledb command to update records.

UPDATE StoreA
SET
Market = ?
,Segment = ?
,Quantity = StoreA.Quantity + ?

Am getting bellow errors while running package .

[OLE DB Command [39]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

[OLE DB Command [39]] Error: There was an error with OLE DB Command.Inputs[OLE DB Command Input].Columns[Market] on OLE DB Command.Inputs[OLE DB Command Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

[OLE DB Command [39]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Command.Inputs[OLE DB Command Input]" failed because error code 0xC0209069 occurred, and the error row disposition on "OLE DB Command.Inputs[OLE DB Command Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Is their any mistake in script or how to resolve this issue

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
{count} votes

Accepted answer
  1. Bala Narasimha Challa 466 Reputation points
    2021-08-19T04:07:54.42+00:00

    Thanks for response and support.

    Am able to figure out this issue.

    I did small changes in oledb command query and it's working fine.

    Original Query :
    UPDATE StoreA
    SET
    Market = ?
    ,Segment = ?
    ,Quantity = StoreA.Quantity + ?

    Modified Query :
    UPDATE StoreA
    SET

    Segment = ?
    ,Quantity = StoreA.Quantity + ?
    WHERE Market = ?

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Bala Narasimha Challa 466 Reputation points
    2021-08-18T04:24:23.107+00:00

    Thanks for quick response.

    Schema of table
    CREATE TABLE [dbo].StoreA ON [PRIMARY]

    Please find out attached screenshots for reference.
    124114-capture20210818082301999.png

    124090-capture20210818082339870.png

    124162-capture20210818082346428.png

    124154-capture20210818082423179.png

    124155-capture20210818082432398.png

    124172-capture20210818082447576.png

    124173-capture20210818094727367.png


  2. ZoeHui-MSFT 33,626 Reputation points
    2021-08-20T01:53:15.053+00:00

    Hi @@Bala Narasimha Challa ,

    Glad to hear that your issue has been resolved, you could mark it as answer so other user with similar problem could see this easier. :)

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments No comments