ssis populate numerous tables with the idnetity value from 1 table

Question

ssis populate numerous tables with the idnetity value from 1 table

chris williams-431556

Grasshopper

Points: 20
More actions
April 19, 2007 at 6:48 pm

#195417

I have already posted this question earlier but I thought it might be unclear
so i will try to word my problem differently
I am trying to do this in ssis only !!
I have a xml file that I am mapping to numerous tables
the elements in the xml are mapping fine to the tables I just can not figure out how to get the id across the various tables.
ie. the tables i am loading
agent_table - columns : ID and Agentnumber
name_table - columns : ID and NAME
master_table - columns : ID and policynumber (in this table ID identity is true)
the other tables identity on the ID is column is false
I use the xml schema to map the applicable columns and tables directly
how do i get the ID in the master_table that will be incremented when a row is added(a new policy) to map to the agent and name table
a policy can have numerous agent and names
policy 123456
agent abc, def
name joe, bob, tom
I would need my tables to look like
master_table id=1, 123456
1 row
name_table id=1, joe
id=1, bob
id-1, tom
3 rows
agent_table id=1, abc
id=1, def
2 rows
I am adding this process to an existing database so i can not change the database design
Thank you for your time, Chris.

Viewing 5 posts - 1 through 4 (of 4 total)

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

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 1

Hey Chris,

I'm sorry to say that I'm no help, only that I feel your pain. I have now spent 4 hours trying to do something that would have taken 5 minutes in DTS (seriously!). But, I am wondering if you've come up with something, or somebody else will have mercy on us and share their wisdom.

How can I do a per-row lookup to get the next available "ID" value when/before inserting into the destination where the Primary key is a non-identity integer?

Jamie Thomson SSChampion Points: 11805 More actions · Answer 2

I *think* this can *probably* be achieved in an OLE DB Command component.

You can issue SQL from the OLE DB Command that gets the maximum existing value and then use that value in your insertion (from the same OLE DB COmmand component.

This will do singleton inserts and a lookup for every single row (meaning very poor performance) but I suspect that will be no different from DTS.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 3

OK... so, I should replace my OLE DB Destination object with the OLE DB Command object to do the lookup and insert. ? I'm trying to figure out why this ability has been "removed" from SSIS. I understand the performance issue, but it seems like this was a regular necessity when programming DTS. Maybe I just got used to bad code. ?

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

Hi Josh,

I've just had a go at what I suggested above and, whilst I still think tis possible, there are nuances to gettig it working so I'm not goig to bother.

Instead, I'll tell you another way of achieving the same that doesn't copy the DTS way of doing it. You can generate your own IDs within the pipeline as described here: http://www.sqlis.com/37.aspx. If you need to get the maximum existing value you can can do that using an Execute SQL Task and pass it to the script component using a variable.

Alternatively, you can make it even easier on yourself and just use this add-in: http://www.sqlis.com/93.aspx

Both of these solutions wil perform MUCH quicker than DTS because they won't be doing singleton inserts (which the DTS solution will be using) and it won't have to do a lookup for each row (which I suspect the DTS solution might be doing).

Regards

Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson