question

FarrukhTashpulatov-8468 avatar image
0 Votes"
FarrukhTashpulatov-8468 asked YitzhakKhabinsky-0887 answered

unique IDs for data from XML file using SSIS

I have an XML file as a data source and I am willing to extract the data and store it in SQL Server database, and I am using SSIS for that. There is a XML Source data flow component in SSIS that works perfectly fine for this. The problem is that the data in that XML file does not have any IDs/Primary Keys/Foreign Keys whatsoever. XML Source component generates IDs on the fly, and it works perfect for joining the tables, however I will have more XMLs in the future, and the data from there should be appended to existing tables. In that case those IDs that SSIS generates will be duplicates of those that are in the tables already.

Is there a way I can make SSIS generate unique IDs with respect to those that exist in the tables already, to avoid any possible duplicates?

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @FarrukhTashpulatov-8468,

You may use Script Component to meet your need.

Please refer the below link for details.

https://svangasql.wordpress.com/2012/10/10/generate-uniqueidentifier-with-ssis/

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Zoehui-MSFT ,
Thank you for your answer,

How does that method make sure the GUID value will actually be unique, and there are no IDs with the same value existing in the table already?

0 Votes 0 ·
ZoeHui-MSFT avatar image ZoeHui-MSFT FarrukhTashpulatov-8468 ·

Hi @FarrukhTashpulatov-8468, a GUID should always be unique as said here.


0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered

Hi @FarrukhTashpulatov-8468,

  • What SSIS provides is enough to join different fragments of input XML.

  • SQL Server has IDENTITY property for columns. It will generate unique values upon INSERT into a table.

Check it out here: create-table-transact-sql-identity-property


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.