i am loading multiple table from one Db to another Db using Oledb src and destin but i just want to keep Pk & Fk on detonation table like src. i dont want to do it manually or something like that

Then you need to ensure you load the parent tables 1st then the children ones.Arthur My Blog

follow Arthur's suggestion, you will be fine. the only problem might occur when your primary table have a INDENTITY primary key, then you can do one of these: - remove IDENTITY from primary table, load data from source database into destination or - leave the IDENTITY in primary table, but add another field in the destination table to store Source Primary Key value, you can call it as AlternateKey. load the primary table first, and then load second table with a lookup on primary table (in lookup set joining field as AlternateKey and then fetch the new PK of the table). If you just want to have destination structure similar to source and you want to implement an easy method go for method #1http://www.rad.pasfu.com

follow Arthur's suggestion, you will be fine. the only problem might occur when your primary table have a INDENTITY primary key, then you can do one of these: - remove IDENTITY from primary table, load data from source database into destination or - leave the IDENTITY in primary table, but add another field in the destination table to store Source Primary Key value, you can call it as AlternateKey. load the primary table first, and then load second table with a lookup on primary table (in lookup set joining field as AlternateKey and then fetch the new PK of the table). If you just want to have destination structure similar to source and you want to implement an easy method go for method #1http://www.rad.pasfu.com

Here are the steps you can do to achieve what you want: 1 - Before your data flow task add "Execute SQL Task" to set "Identity insert" to ON to your tables in which you are inserting data such as: SET identity_insert dbo.<TableName> ON 2 - Change your dataflow task to insert the primary keys (identities) as well. 3 - After the data flow task add another "Execute SQL Task" to set "Identity insert" back to OFF for those table such as: SET identity_insert dbo.<TableName> OFF Here is some more information about identity_insert: http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx Hope this helps-Please mark the post as answered if it answers your question

Here are the steps you can do to achieve what you want: 1 - Before your data flow task add "Execute SQL Task" to set "Identity insert" to ON to your tables in which you are inserting data such as: SET identity_insert dbo.<TableName> ON 2 - Change your dataflow task to insert the primary keys (identities) as well. 3 - After the data flow task add another "Execute SQL Task" to set "Identity insert" back to OFF for those table such as: SET identity_insert dbo.<TableName> OFF Here is some more information about identity_insert: http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx Hope this helps-Please mark the post as answered if it answers your question