Share via

How to load XMLs from a directory into the database

Naomi Nosonovsky 7,886 Reputation points
2024-02-23T14:44:19.24+00:00

Hello, I want to load XMLs from a directory containing over 12000 files into SQL Server database. To be precize I actually need to extract just two column values from these XMLs into a table. I thought I'd try to load the whole XML into XML column of some table and then parse these XMLs using XPATH later. I set up foreach loop container to enumerate over the files, but then I'm at loss as what to do next. Here is what I tried which doesn't work and I don't know why: User's image

where I try to start from the derived column like this User's image

where varFileName is the variable holding the full file name used in the foreach loop. Can you please help me to figure out how to achieve my goal? Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,014 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,593 questions
Accepted answer
  1. Debarchan Sarkar - MSFT 1,131 Reputation points Microsoft Employee
    2024-02-25T01:47:09.71+00:00

    To load XML files from a directory into a SQL Server database using SSIS (SQL Server Integration Services), you need to use the Foreach Loop Container for file enumeration and the XML Source task to read the XML content. Here's a step-by-step guide on how to achieve this: Create a new SSIS package or use an existing one: In SQL Server Data Tools, create a new Integration Services project or open an existing one. Add a new SSIS package or use an existing one. Configure Variables: Create two variables in the SSIS package. One for holding the directory path (User::FolderPath) and another for holding the current filename during iteration (User::FileName). Set up Foreach Loop Container: Drag and drop a "Foreach Loop Container" onto the Control Flow design surface. Double-click it to configure. Set Enumerator as "Foreach File Enumerator". In "Directory", put your variable User::FolderPath. Retrieve file name: select "Fully qualified". In "File Spec", put "*.xml" to fetch only XML files. In "Variable Mappings", map index 0 to User::FileName. Use Data Flow Task within Foreach Loop Container: Inside the Foreach Loop Container, add a "Data Flow Task". This allows you to perform data transformations such as loading the XML content for each file. Configure XML Source and OLE DB Destination: Inside the Data Flow Task, drag and drop an "XML Source" and an "OLE DB Destination". Connect the XML Source to the OLE DB Destination. Double click the XML Source to configure. In the XML Location, select "XML file from variable" and set the variable as User::FileName. If your XML requires an XSD, provide it in the "XSD Location". In Columns, select the required elements to be extracted from the XML. Similarly, double-click the OLE DB Destination to configure it. Make sure you select the correct connection manager, target table, and map the input columns appropriately.

    Remember that XML files must have the same structure across the folder because XML Source task uses a schema (XSD) that describes the structure of the XML documents. For your next steps: Set up and configure the Foreach Loop container and the contained Data Flow task. Configure the XML Source and OLE DB Destination tasks. Run the SSIS package and validate if the XML data is correctly inserted into your SQL Server database.

    Please let me know if you need further assistance or if there are more specific aspects of your scenario that I should consider.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.