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.
| Author |
Topic |
|
sujosh
Yak Posting Veteran
55 Posts |
 Posted - 2005-07-06 : 18:08:16
|
| There are 3 tables A/B/C. My DTS inserts from table A into table B and it works fine. I also want to insert into table C with the the PK value of Table B as the FK in table C.How can I do this in DTS?Thanks |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-07 : 06:37:06
|
| you edit the existing package, and i hope it's not from a wizard, add an execute sql task object (you provide the query here), link it from B with an 'on success workflow' and save--------------------keeping it simple... |
 |
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2005-07-07 : 11:22:24
|
| Here I have a Microsoft OLe DB provider for Oracle connection and i have Microsft OLE DB provider for SQl Server. There is a Transform data task which is mapped to get columns 1/2/3 to populate into Table C. Now for each row that gets inserted eg.After the 1st row gets inserted in Tab B I want a row to be inserted into tab C with PK of B ( using Scope_identity) get inserted into Tab C as the FK.Can I do this using DTS? This is my problem? I am sure there is a way but I have not done this before? I can write a SP and get this done, but I really want to learn a way todo this using DTS. Thanks for all your help Jen! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-08 : 03:32:51
|
there is a way, but consider this...do you want to commit successful rows inserted and rollback unsuccessful rows? or rollback everything if an error is encountered?if yes to 1st question, since you have all tables in one database, you can create insert triggers in A and B.with every insert in A, you get inserts in B, then B inserts in Cif yes to 2nd question, i suggest the original approach, this way you process one table at a time and not locking up or affecting resources for A,B and C simultaneously.from experience, unless you have some other data being manipulated before inserting into C, use the 2nd approach. Or better yet, try both approach and review the difference yourself  HTH--------------------keeping it simple... |
|
|
|
jmjohnson
Starting Member
1 Post |
Posted - 2005-07-08 : 08:22:43
|
| Use lookups and make your tansformation type as Active X Script. You could achieve this easilyJohsnon |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2005-07-08 : 15:32:48
|
| "you edit the existing package, and i hope it's not from a wizard, add an execute sql task object (you provide the query here), link it from B with an 'on success workflow' and save"Jen, I am trying to understand, can you please verify by saying "what do you mean by not from a wizard"? I created my DTS using create a new package and created two Microsoft Ole DB connections and set up "data transform task" between those two. Can you please tell me how I can have a execute sql task object inserted in this. When I tried to I am not able to do this. Please let em know how I can add the execute sql task. Thanks |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-10 : 23:03:49
|
| ok, you're creating your own package.or did you? on the left side of the window, you'll see an tool bar. Under the task group, mouse over each of the objects to search the name (the task i'm talking about is the database object with two red arrows).double click on that, for the workflow, look at the menu barHTH--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Inserting 2 tables using DTS