Search code examples
xmlssisado.netsql-server-2012dataflowtask

Import XML with multiple Tables to SQL Server

I got XML with multiple tables to import to SQL Server to do this, I follow this steps:

  • Add data flow task to control flow tab.

On data flow tab (when double clicked the data flow task):

  • Add XML source, configure the XML location and XSD location.

  • Add Ado.net destination, connect source to destination, Input Output Selection will show up, select table to import to SQL.

  • Configure connection Manager. Under User table or view, click "New" button to generate new table to database where the xml table values will be inserted.

  • Execute package. New table has added to my database with some values.

My goal is to import table to my Database, my problem is I have lots of table from my xml schema, and it will take time to do the steps 2-5 repeatedly. Is there a way to resolve this.

Note: I don't have a ready made table on my SQL server that's why I generate new table to my database matched to XML schema. (step 3).

Solution

  • The method you described is the fastest / easiest way I know of for this task.

    If you look at it from the other end of the telescope, you are:

    • shredding a very complex XML schema into separate SQL tables
    • generating tables that perfectly match the XML structure, including foreign key relationships
    • loading the tables with data
    • building a repeatable process
    • it's probably taking you under a minute per table
    • not having to type anything
    • using standardised tools that minimize your test effort

    Happy clicking ...