title: SSIS Migrating data to Azure from multiple sources
body: <p>The scenario is this: We have an application that is deployed to a number of locations. Each application is using a local-instance of SQL Server (2016) with exactly the same DB schema.</p>
  
  <p>The reason for local-instance DBs is that the servers on which the application is deployed will not have internet access - most of the time.</p>
  
  <p>We were now considering keeping the same solution but adding an SSIS package that can be executed at a later time - when the server is connected to the internet.</p>
  
  <p>For now let's assume that once the package is executed - no further DB changes will be made to the local instance.</p>
  
  <p>All tables (except for many-to-many intermediary) have an INT IDENTITY primary key.</p>
  
  <p>What I need is that the table PKs get auto-generated on the Azure DB - which I'm currently doing by setting the mapping property to  for the PK, however I would also need all FKs pointing to that PK to get the newly generated ID instead of pointing to the original ID.</p>
  
  <p>Since data would be coming from multiple deployments, I want to keep all data as new entries - without updating / deleting existent records.</p>
  
  <p>Could someone kindly explain or link me to some resource that handles this situation?</p>
  
  <p>[ For future references I'm considering using UNIQUEIDENTIFIER instead of INT, but this is what we have atm... ]</p>
  
  <p><strong>Edit: Added example</strong></p>
  
  <p>So for instance, one of the tables would be Events. Now each DB deployment will have at least one Event starting off from Id 1. I'd like that when consolidating the data into the Azure DB, their actual Id is ignored and instead get an auto-generated Id from the Azure DB. - That part is Ok. But then I'd need all FKs pointing to EventId to point to the new Id, so instead of e.g. 1 they'd get the new Id according to Azure DB (e.g. 3). </p>
  
  <p><img src="https://i.imgur.com/DhyPwth.png" alt="diagram"></p>