0

I have a remote XML file, which is zipped (approx 100MB in size). I need to download, extract, read, parse and import into SQL Server.

Before starting coding this solution (in Python), is there any ready-made utility which could do that? Notice that this needs to run on a scheduled basis (preferably as service) or Windows Schedule.

What's really important is that it needs to be really fast!

Thank you, Giorgoc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Giorgoc
  • 3
  • 1
  • 1
    I would start looking at SSIS first. You can set up a package to import the XML and load it into your SQL Server database and i.m.o. would be the simplest and quickest.. Then, using the job agent, you can call the package based on an interval... eg: every 10 to 20 seconds. – Sean H Nov 07 '16 at 10:00
  • what do you mean 'set up a package' to import XML? Can a 'package' download, extract and then import ?? – Giorgoc Nov 07 '16 at 15:13
  • Hi Giorgoc, an SSIS package (like a container) can do many built in tasks and can also access c# scripts. It will be possible to download the XML file, extract and transform the data, and then load it into a SQL Server table. You can do this all in one package or split the tasks out into multiple packages which would be better for debugging i.m.o. – Sean H Nov 08 '16 at 08:57

1 Answers1

0

Following on my comments:

You can quite easily do this with SSIS.

  1. Download the remote XML file... Here is an example: How to read a remote xml file from SQL Server 2005
  2. you can then do transformation on the data if needed by using transformation tasks
  3. Using SSIS to load the XML data into the SQL Server DB... Here is an example: How to load an XML file into a database using an SSIS package?

Hope they point you in the right direction and help you in your tasks.

Community
  • 1
  • 1
Sean H
  • 267
  • 2
  • 6