MS SQL SSIS xml source task

I am importing xml multiple times a day from a vendor. However when SSIS created the ID's for nested XML data it is not unique.   So importing the first time and I get 3-4 records it looks fine. However subsequent imports all use the same ID's so it isn't unique, how do I go about changing this as I cant find anything about it.
LVL 6
CaptainGibletsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NazermohideeenCommented:
From what I understand, I think you might be able to add a Derived column and add a GUID unique ID, which will be unique all the time.
0
CaptainGibletsAuthor Commented:
But that would only work for the rows that go into each table. The way SSIS does it it links all the data together using more than 1 primary key and links them all together. If i added a derived column with a GUID it wouldnt work as some columns that are auto created have the same value 4-5 times to link them to certain records etc.
0
NazermohideeenCommented:
Need more information about your SSIS. May be a screenshot or something.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CaptainGibletsAuthor Commented:
Will post back tomorrow when in office but it creates 8 tables which all use some sort of unique id and sometimes a non unique. For example I have a questionanswerid which then l
0
CaptainGibletsAuthor Commented:
Sorry was side tracked.

here is an example of the XML file
C--New-folder-campaneo-Download.xml
0
CaptainGibletsAuthor Commented:
Still need help with this if anyone has any ideas.
0
ValentinoVBI ConsultantCommented:
I have the impression you're currently adding the data from the files to the same tables that already contain data from the previous files, without any staging phase.  Is that right?

To avoid your issue you should work with staging tables.  For each file import they need to get truncated.  That way you import an XML file into empty destination tables and you won't face that duplicate ID issue. Once the data is in the staging tables you can use a second Data Flow transform to extract it from there and move it into the final tables, which might be modeled a bit different, depending on your requirements.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.