0

So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a REALLY large amount of memory if I am to use an SSIS component (i.e.: XML Source), it might cause a timeout (correct me if I'm wrong, but as far as I know, using the components on SSIS loads the content of the XML into memory - with a file that big it will surely cause errors). My approach then is:

  • Use a Script Task to parse the XML data using XML Reader (XML Reader by far is the best approach, since it parses the XML on a forward, non-cached approach)
  • Insert the data on a DataTable
  • Every 500,000 rows on the DataTable, insert the contents to the database using SqlBulkCopy, then clear the contents of the DataTable

My problem is, currently, I tried it to parse another file with the size of 200GB, and it's running on around 13.5M / 1 hour - and I don't know if it's still fine with that run time. It sure solves my problem - but it's not too elegant, I mean, there should be other ways.

I'm looking on other approaches, like:

  • Dividing the large XML files into small pieces of CSVs (around 20GB) then use an SSIS Data Flow task
  • Use INSERT script every new rows

Can you help me do decide which is best? Or suggest any other solutions.

Every answer will be very much appreciated.

EDIT

I forgot to mention - my approach will be dynamic. I mean, there are many tables that will be populated with large sized XML files. So, using a Script Component as source might be not so useful, since I still need to define the output columns. But still, will give it a try.

  • Please, don't cross post – billinkc Jul 26 '15 at 23:12
  • You may find it difficult to get an answer that is both highly flexible and highly efficient. Those tend to be competing requirements and you end up having to lean one way or the other. Consider reading something like this and then taking another look at your needs: henkvandervalk.com/… – Micah Nikkel Jul 27 '15 at 15:10
1

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.

| improve this answer | |
  • please see my update on the question. – Dustine Tolete Jul 26 '15 at 5:07
1

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well:

enter image description here

This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source streams but I can't find any documentation to confirm it.

Which version of SQL Server are you using? I don't think the XML Task in Control Flow is an option here, so try the XML Source in Data Flow before you do any custom script and let us know how you get on.

| improve this answer | |
0

Create a script component as a data source. You can probably re-use a lot of your code that you used to create the script task. Then you can set your batch size.

| improve this answer | |
  • the approach should be dynamic - there are multiple tables that will use this approach, and i can afford to have it specific to one. using script component on a data flow task will have me configuring the output columns. – Dustine Tolete Jul 27 '15 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.