How to update a destination table in ssis

Question

Post reply

How to update a destination table in ssis

Viewing 5 posts - 16 through 19 (of 19 total)

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

abhish_216 SSC Veteran Points: 237 More actions · Answer 1

Yes the destination table name is emptem.

I can explain the entire process stepwise.

1.selected a source table emp having empno,ename,mgr,sal,comm.

2.took look up transformation in data flow task and gave emptem as lookup table.here the emptem table has only two columns empno,ename.i mapped them to emp table values.

3.now i took the oledb command and choose look no output condition with lookup table.

gave the update command

update emptem

set ename = ?

where empno=?

4.executed the package.

it is running fine with no errors and i am unable to view the result.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

So you mean that there aren't any rows updated?

If you run the package, you can see how many rows were sent to the OLE DB Command.

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

abhish_216 SSC Veteran Points: 237 More actions · Answer 3

execution 1. initially i am getting all the rows in emp to emp tem.

execution 2: i inserted a new row into emp and executed the package again now only the new row is inserted into the emptem.

execution 3: now i updated a row in emp and executed the package again it is giving me the following errors

Error: 0xC0202009 at Data Flow Task 6, OLE DB Destination [57]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_EmpTem'. Cannot insert duplicate key in object 'dbo.EmpTem'.".

Error: 0xC0209029 at Data Flow Task 6, OLE DB Destination [57]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (70)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (70)" 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 6, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (57) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (70). 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 SSC Guru Points: 259085 More actions · Answer 4

In your explanation you never said anything about an OLE DB Destination.

I think you coupled your no match output to your updates and your match output to your inserts. It should be the other way around.

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

abhish_216 SSC Veteran Points: 237 More actions · Answer 5

Hi Koen,

i finally got a solution after 4 hrs. I took the source as emp

took a look up transformation and gave the same emp table as lookup table

then i matched the look match output to the oledb command

and gave a update statement to update the emptem.

Its working fine now.

Thanks for your support mantxtPost_CommentEmoticon(':-)');