XML : Slow/Poor Performance on SSIS with xml

I'm new to SSIS and I got this problem:

I've a little over 100.000 xml-files, each of them around 16kb big. They have about 200 columns and one primary key. Some of them are only for update. I've to fill a database with the data, but it takes about 1,5 second for each file to load/update. I'm using VS 2015 and MS SQL. I only want to create the database within working hours, right now it would take about 44 hours, which seems ridiculous. (After that there will be only about 10 xml-files per day for updating.)

My SSIS ControlFlow looks like:

enter image description here

My SSIS DataFlow looks like: enter image description here

I've no idea how to get the performance better.

Things I tried: Setting Ole DB = Rows per batch 5000 and use fast load

Even when I do only insert the xml (which ends in an error as I do not update), it needs a lot time (about 1 second per file).

Any help would be appreciated.

No comments:

Post a Comment