Share via

Loading XML file into XML column using SSIS

Naomi 7,361 Reputation points
2022-05-27T14:47:15.743+00:00

Hi everybody,

I believe I already asked this question before, but I have a need for it again and could not easily find info, also hoping for new ideas.

I want to be able to pick an XML file from a directory (XML file will be read from a variable) and read it into XML column (and save some other info in other columns) using SSIS.

Do you have a tutorial I can follow to do this?

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.
13,002 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,479 questions
Accepted answer
  1. Yitzhak Khabinsky 25,286 Reputation points
    2022-05-27T15:45:45.253+00:00
    2 people found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 34,186 Reputation points
    2022-05-30T02:45:47.31+00:00

    Hi @Naomi ,

    You may also refer to below links:

    https://stackoverflow.com/questions/5152671/how-to-load-an-xml-file-into-a-database-using-an-ssis-package

    https://www.sqlshack.com/import-xml-documents-into-sql-server-tables-using-ssis-packages/

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. Naomi 7,361 Reputation points
    2022-06-02T18:14:22.383+00:00

    For some unknown reason SSIS just doesn't use variable correctly in run-time and keeps setting it to 1. It is weird because in preview mode it works. I tried varSQL and I tried direct command like this:

    declare @load_file_detail_id int = ?;
    declare @input_file varchar(200) = ?;

    select @input_file as FilePath, @load_file_detail_id as load_file_detail_id, 'c:\Logs\277CAExampleXML.xml' as FilePath4;

    When I preview, I see correct filename (same as hardcoded value) in FilePath variable. However, when I click green arrow to run the package from VS, it keeps using 1 as the filePath and I don't know what is wrong. I re-created this thing 10 times already :( May be it's because of the bug in VS I'm using but it's very hard to get updates here as it has to go through a service request and someone has to install updates.

    My VS is Microsoft Visual Studio Professional 2019
    Version 16.11.3