Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to update a destination table in ssis Expand / Collapse
Author
Message
Posted Monday, April 02, 2012 11:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 05, 2012 4:36 AM
Points: 11, Visits: 29
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?
Post #1277076
Posted Tuesday, April 03, 2012 12:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
Is that the only error that you get?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1277082
Posted Tuesday, April 03, 2012 12:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 05, 2012 4:36 AM
Points: 11, Visits: 29
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.
Post #1277084
Posted Tuesday, April 03, 2012 12:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1277086
Posted Tuesday, April 03, 2012 12:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 05, 2012 4:36 AM
Points: 11, Visits: 29
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.
Post #1277089
Posted Tuesday, April 03, 2012 12:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
Are both the source and destination SQL Server?
What datatype is used in SSIS?
What collation do you use in the source and destination?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1277091
Posted Tuesday, April 03, 2012 12:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 05, 2012 4:36 AM
Points: 11, Visits: 29

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
Post #1277094
Posted Tuesday, April 03, 2012 2:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 05, 2012 4:36 AM
Points: 11, Visits: 29
Any idea? i have been debugging for past 1 hr but couldn't get the exact result.

Thanks,
Abhi
Post #1277126
Posted Tuesday, April 03, 2012 2:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1277128
Posted Tuesday, April 03, 2012 2:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 05, 2012 4:36 AM
Points: 11, Visits: 29
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.
Post #1277136
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse