April 7, 2011 at 10:48 am
I am trying to figure out how i can create an SSIS package which pulls out data from one table on source and inserts into multiple tables on Destination(having FK constraints). After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.
For example, I have a table Users. The primary key(userID)is an Identity column. Once the SSIS insert is complete, the bulk load of new users has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, UserDetails - it has a foreign key for the user Table with id column and a foreign key to its child table called department being added. And, as part of this I will need to get the latest ID value and store them in variables.
Does anyone has any link which details out the steps how this can eb implemented thru SSIS.
TIA
April 7, 2011 at 11:12 am
I have implemented something similar using a script component that calls a stored procedure. The stored procedure inserts a record and returns the new identity value, which can then be added to the data flow and used downstream
I posted an article about this solution.
http://www.sqlservercentral.com/articles/Data+Warehouse/71233/[/url]
Take a look at the sections that describe the following:
- Stored procedure (Step 1 - Create Database Objects)
- Script component to call the procedure (Step 2, item #3)
- Using output of procedure and script component in the Data Flow (Step 2, item #4)
This should give you some ideas.
Bob
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply