Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to update a destination table in ssis


How to update a destination table in ssis

Author
Message
abhish_216
abhish_216
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16524 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
abhish_216
abhish_216
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16524 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
abhish_216
abhish_216
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16524 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
abhish_216
abhish_216
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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
abhish_216
abhish_216
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 29
Any idea? i have been debugging for past 1 hr but couldn't get the exact result.

Thanks,
Abhi
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16524 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
abhish_216
abhish_216
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search