How to insert,update,delete in destination table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to insert,update,delete in destination table

Author

Topic

pradeep_21
Starting Member

9 Posts

Posted - 2012-05-11 : 07:09:01

Hi All,

I'm new in ssis how to insert,update and delete operation from source table into destination table using SSIS package.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 16:00:23

One way is use Lookup task along with oledb command and oledb destination tasks. The lookup task will have multiple outputs for inserts and updates. Another way is to use SCD wizard task if table is failry small. for large tables you'll have performance issues using this.

for delete operations do you have a logical deletion logic or physical deletion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pradeep_21
Starting Member

9 Posts

Posted - 2012-05-14 : 01:03:19

Hi visakh,

Thanks for replying....

U mean Lookup --> oledb command --> oledb destination all 3 components need to hook up in green color.

In a lookup component is it connect source table?

What is mean by SCD Wizard.

Logical deletion only. (Based on source table if its not in destination table that row should be delete.

In my table ID column in PK.

thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 23:22:04

quote:
Originally posted by pradeep_21

Hi visakh,

Thanks for replying....

U mean Lookup --> oledb command --> oledb destination all 3 components need to hook up in green color.

In a lookup component is it connect source table?

What is mean by SCD Wizard.

Logical deletion only. (Based on source table if its not in destination table that row should be delete.

In my table ID column in PK.

thanks in advance.

Yes. layout is correct

Lookup will connect to table you want to compare too
In case of updates,inserts it will be destination

for physical deletes it will be source

SCD is slowly changing dimension

do you understand difference between logical and physical deletion?
(Based on source table if its not in destination table that row should be delete.
The above sounds like physical deletion to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pradeep_21
Starting Member

9 Posts

Posted - 2012-05-22 : 08:16:19

I build a DTS package like this (OLE DB Source --> Multicast -->OLE DB Commend)

In my package source table and destination table datatype is different.

So is it possible to transfer the data from Source to Destination?

Because i am getting validation warning error when running a package.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources