How to update a destination table in ssis

  • Hi Everyone,

    I have a scenario where emp table has id,name,description.

    i need to copy only id and name columns to emptem.

    when a newrecord is added in emp the same should be inserted into emptem without any duplicates.

    i have done this using lookup by specifying emptem table in lookup and matched the destination to emptem with no matched columns.

    Now i have a new scenario where if any of record in emp is updated i need the updated value in emptem.

    i am continuing the same scenario as above and matching lookup table to oledb command. i have given the update stmt update emptem set empno = ? where empname=?

    i am getting the error as follows

    Error: 0xC0202009 at Data Flow Task 5, OLE DB Command [177]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

    Any suggestions?

  • Is that the only error that you get?

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

  • Apart from the above one i have the following errors also

    Error: 0xC020901C at Data Flow Task 5, OLE DB Command [177]: There was an error with input column "ename" (193) on input "OLE DB Command Input" (182). The column status returned was: "The value could not be converted because of a potential loss of data.".

    Error: 0xC0209029 at Data Flow Task 5, OLE DB Command [177]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Command Input" (182)" failed because error code 0xC0209069 occurred, and the error row disposition on "input "OLE DB Command Input" (182)" 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.

    Error: 0xC0047022 at Data Flow Task 5, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (177) failed with error code 0xC0209029 while processing input "OLE DB Command Input" (182). 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.

  • abhish_216 (4/3/2012)


    Apart from the above one i have the following errors also

    Error: 0xC020901C at Data Flow Task 5, OLE DB Command [177]: There was an error with input column "ename" (193) on input "OLE DB Command Input" (182). The column status returned was: "The value could not be converted because of a potential loss of data.".

    This is the import one, as it actually explains what went wrong.

    There's an issue with the ename column. What is the datatype used in SSIS and what datatype has it in the database?

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

  • Ya the datatype in destination is varchar(50) and source is varchar(20). but i changed them and executed the package again it is giving me the errors again.

    Error: 0xC0202009 at Data Flow Task 5, OLE DB Command [177]: 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 10.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

    Error: 0xC020901C at Data Flow Task 5, OLE DB Command [177]: There was an error with input column "ename" (193) on input "OLE DB Command Input" (182). The column status returned was: "The value could not be converted because of a potential loss of data.".

    Error: 0xC0209029 at Data Flow Task 5, OLE DB Command [177]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Command Input" (182)" failed because error code 0xC0209069 occurred, and the error row disposition on "input "OLE DB Command Input" (182)" 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.

    Error: 0xC0047022 at Data Flow Task 5, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (177) failed with error code 0xC0209029 while processing input "OLE DB Command Input" (182). 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.

  • Are both the source and destination SQL Server?

    What datatype is used in SSIS?

    What collation do you use in the source and destination?

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

  • Are both the source and destination SQL Server? -- yes on same sql server

    What datatype is used in SSIS? -- Same data types. int for empno,

    varchar(20) for ename

    What collation do you use in the source and destination? -- SQL_latin1_General_CP1_C1_AS

  • Any idea? i have been debugging for past 1 hr but couldn't get the exact result.

    Thanks,

    Abhi

  • abhish_216 (4/3/2012)


    Are both the source and destination SQL Server? -- yes on same sql server

    What datatype is used in SSIS? -- Same data types. int for empno,

    varchar(20) for ename

    What collation do you use in the source and destination? -- SQL_latin1_General_CP1_C1_AS

    No, the datatype in SSIS is either DT_STR or DT_WSTR. Big difference.

    Are there special characters in your data?

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

  • I don't have any special characters in data.They are simple names like 'ran','san' and empnos are 1,2,3

    How to check the data types in ssis.

    i am just checking the datatypes in source and destination tables.

  • Right-click on an arrow in the dataflow, choose dataviewers and select metadata.

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

  • i have checked the data types.

    empno data type is DT_I4 and ename datatype is DT-STR for the entire data flow.

  • abhish_216 (4/3/2012)


    i have checked the data types.

    empno data type is DT_I4 and ename datatype is DT-STR for the entire data flow.

    All seems OK.

    But there must be some sort of implicit conversion going on in the OLE DB command, otherwise it wouldn't fail.

    Open up the advanced editor of the OLE DB Command and check the datatype of every column referencing the ename column: input columns, output columns, external columns (in the input and output properties tab)

    Verify that the mappings are done correctly.

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

  • Ya there was an implicit conversion taking place.the data types are interchanged in the external output columns.

    I modified the entire thing and the execution is done without any errors.

    But i am not able to get the desired output. emptem is now empty.

  • abhish_216 (4/3/2012)


    But i am not able to get the desired output. emptem is now empty.

    emptem is the table right?

    You're doing an update statement in the OLE DB command, so the problem is somewhere else.

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

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

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