Specify How Primary Keys Are Generated During Ssis Xml Import

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

Solution 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.

Solution 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