ssisazure-data-factory

Data transformation using ADF or SSIS

I have a CSV file feed which contains information about Product, Order and Customer inside a single file. I have to insert/update Product, Order and Customer data into their respective tables in the database. There are about 10 database tables.

Sample Data:

enter image description here

I have two options:

  1. Split data using ADF/SSIS, get unique rows, insert/update data and their foreign keys using ADF SQL task?

  2. Copy CSV file into a staging table. Use SQL stored procedure to get data from staging table and transfer them to their respective tables.

Which option is better in terms of development speed and future maintenance?

Solution

  • I would suggest to go the way of ELT i.e load the data from the file into staging table and then do the split/transformations via Stored procedure.

    The reason is the copy activity within ADF is not transaction bound, so anyhow post the transformations as well; you would have to dump the data into the respective staging tables and then use stored procedure to load from staging into final table (bound within transactions)

    So the #2 approach would be more quicker and easier ensuring data transaction boundness