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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ValentinoVConnect With a Mentor BI 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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
NazermohideeenCommented:
Need more information about your SSIS. May be a screenshot or something.
0
 
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
All Courses

From novice to tech pro — start learning today.