Copy multiple CSV Files to SQL Database (validations and foreign keys)

Question

Hi, I'm new here and also using the Data Factory tool and well, I explain.

I managed to insert the information from the csv files to their respective tables, with the activities: Get MetaData, forEach (activity: copy data) . As the example of this topic: how-to-bulk-copy-multiple-csv-files-from-blob-to-mutliple-sql-database-tables-wi . However, the tables in the example are basic and not related.

I have 10 tables. Some of them depend on others (foreign keys). To insert in a table I must first insert in another one, get its identifier and then insert to the second table together with the foreign key.

Besides that, I need to do some validations on some fields.

Is it possible to do this in data factory?

Answer 1

There are many ways you could do it. What Id suggest to try is to sequence the tables in the order that you need.

Let's say the first table was "Addresses" and you then needed the FK value of a specific address in the Customers table. In this case I would:

1. Load the Addresses table
2. Load the Customers data from CSV and then include a Lookup to the Addresses table to match on the non-key columns as required and return the PK value as a new column
3. Load the Customers table discarding the non-key Address columns and only including the PK value to enable the FK relationship.

It is extra steps in your pipeline but will ensure you keep full control of the loading and enforce the relationships