Specify how primary keys are generated during SSIS

Question

The problem is I import data into relational tables where the source of the data is a XML-file + XSD schema. The XML source has several outputs and the relationships between nodes is created by SSIS in columns as order_id (generated primary key value not in XML) and in lets say order details we get the foreign key order_id.

The file gets imported and we have a correct referential integrity between lets say orders and order details but the key is only unique within each file so if same file is imported again the same id is generated/used by ssis.

How can you ensure uniqueness / control how the id is generated.

I have tried to use a identity column as order_id in the order table that works but then it does not propagate down to reference tables like order details...

Example if we have

<order some attributes ...>
    <orderdetails some attributes ...></orderdetails> 
    <orderdetails some attributes ...></orderdetails> 
</order> 

if we just let ssis import the rows we will get something like :

order table

order_id = 4 , rest of data columns

orderdetails table

row 1 orderdetail columns , order_id=4 row 2 orderdetail columns , order_id=4

Everything ok but I don't understand how the apparently semi-random id = 4 is generated

If an incremental key is used I would get (assuming seed starts at 1)

order table

order_id = 1 , rest of data columns

orderdetails table

row 1 orderdetail columns , order_id=4 <--same as previously SSIS generated row 2 orderdetail columns , order_id=4 <--same as previously SSIS generated

I would like the id be 1 for the foreign key in details so I guess there is some intermediary step / setting I have missed in order to maintain referential integrity when importing XML data.

Any pointers would be highly appreciated.

Answer 1

What I suggest is that you import the header as usual, generating internal unique keys using IDENTITY. You should also import the SSIS generated key into this same table.

Now import the details to a seperate staging table, again preserving the SSIS generated key.

Now use the original header table to map from an SSIS generated key to a unique key.

To do this you update a blank field in your staging table with something like this:

UPDATE LineStaging
SET Unique_Key = Header.UniqueKey
FROM Header 
WHERE Header.SSISKey = LineStaging.SSISKey

Now your Unique_Key field contains the correct foreign key. You can copy those staged records accross to your 'real' Line table:

INSERT INTO Line SELECT * FROM LineStaging

There is probably a way to do this on the fly in SSIS but I prefer SQL methods.

Answer 2

Refer the links below which may help you to understand the how to add keys to the tags

http://msdn.microsoft.com/en-us/library/d8wa0tw7(v=vs.71).aspx

http://msdn.microsoft.com/en-us/library/961cwet7(v=vs.71).aspx

hope this may be useful