1

I am attempting to use SSIS (for the first time) to merge data from a CSV file and an existing table (People) in an Azure SQL Database to replace existing data in the table.

The CSV file contains an Id and a CardId. On the existing table in Azure there is a field for CardId which I want to be filled in from the CSV where the two Ids' match.

Here is the Data Flow I am using so far:

enter image description here

I feel that I am close with this solution. The only issue is that when arriving at the People Destination SSIS is trying to add new rows instead of updating existing rows, resulting in a primary key violation error.

I know that I could import the data into a temporary table and update from there. If that is the easiest way to achieve what I want then I will do that, however I feel that if I knew more about SSIS I could use the Data Flow to do this instead.

CBreeze
  • 2,925
  • 4
  • 38
  • 93
  • I think importing into a temp table then doing your logic there will be cleaner and easier to accomplish. Additionally if your data sets you are trying to use get large, doing it like this could use a LOT of memory! – Brad Apr 27 '18 at 13:32

1 Answers1

0

I agree with @Brad, the way you are trying is importing data to the destination it will not do any update. You have to import the CSV content to a temp table, and perform an update query with join to achieve your goal. But if the data is too BIG so it will take much time and consume a LOT of memory.

There are many suggestions for updating huge tables:

Hadi
  • 36,233
  • 13
  • 65
  • 124