Loading XML file into XML column using SSIS

Question

Loading XML file into XML column using SSIS

Naomi Nosonovsky 8,431

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

Maria Estee 1 Reputation point

2022-10-05T22:02:39.367+00:00

May I ask what solution you used to load an XML file into an XML data type column using SSIS? I am looking for a similar solution as I do not want to use XML Source for a couple of reasons (1) I want the entire file in one XML data type column in SQL Server and (2) the XML file has multiple hierarchies and using XML Source, the data would need to come from more than one of those outputs.

Thanks for any insight you can provide into how you solved for this.
Maria
Naomi Nosonovsky 8,431 Reputation points

2022-10-06T13:37:57.99+00:00

I used the first link from Yitzhak's response to load the XML file using column transformation. This is how data flow looks like:

This is how transformation column is set up:
Maria Estee 1 Reputation point

2022-10-06T14:26:06.19+00:00

Hi @Anonymous ,
Thanks so much for the quick reply and screen shots! Much, much appreciated!
Maria
Maria Estee 1 Reputation point

2022-10-06T20:47:46.323+00:00

Hi again @Anonymous ,
Just to confirm, you used this solution: https://www.tutorialgateway.org/import-column-transformation-in-ssis/ but instead of flat file source you used ole db source? And you are importing into an XML data type column in SQL Server? Would you mind sharing the ole db source configuration? I'm getting an error 'xml parsing/illegal xml character'. Perhaps I'm missing something in the ole db source configuration?
Thank you for any guidance! Really appreciate it!
Maria
Rahul 251 Reputation points

2024-01-29T20:57:48.2733333+00:00

Hi @Naomi - I have same requirement as above but my source is xml file source. How can we achieve above solution where the source is xml file source with the xml file placed on the path on some folder. Please guide step-by step solution will be very helpful
Naomi Nosonovsky 8,431 Reputation points

2024-01-29T22:17:42.2566667+00:00

I don't remember the exact solution :( This is how the package looks like

Did you try following the last link from Yitzhak?

Accepted answer

2 additional answers

Your answer

Maria Estee 1 Reputation point

2022-10-05T22:02:39.367+00:00

May I ask what solution you used to load an XML file into an XML data type column using SSIS? I am looking for a similar solution as I do not want to use XML Source for a couple of reasons (1) I want the entire file in one XML data type column in SQL Server and (2) the XML file has multiple hierarchies and using XML Source, the data would need to come from more than one of those outputs.

Thanks for any insight you can provide into how you solved for this.
Maria
Naomi Nosonovsky 8,431 Reputation points

2022-10-06T13:37:57.99+00:00

I used the first link from Yitzhak's response to load the XML file using column transformation. This is how data flow looks like:

This is how transformation column is set up:
Maria Estee 1 Reputation point

2022-10-06T14:26:06.19+00:00

Hi @Anonymous ,
Thanks so much for the quick reply and screen shots! Much, much appreciated!
Maria
Maria Estee 1 Reputation point

2022-10-06T20:47:46.323+00:00

Hi again @Anonymous ,
Just to confirm, you used this solution: https://www.tutorialgateway.org/import-column-transformation-in-ssis/ but instead of flat file source you used ole db source? And you are importing into an XML data type column in SQL Server? Would you mind sharing the ole db source configuration? I'm getting an error 'xml parsing/illegal xml character'. Perhaps I'm missing something in the ole db source configuration?
Thank you for any guidance! Really appreciate it!
Maria
Rahul 251 Reputation points

2024-01-29T20:57:48.2733333+00:00

Hi @Naomi - I have same requirement as above but my source is xml file source. How can we achieve above solution where the source is xml file source with the xml file placed on the path on some folder. Please guide step-by step solution will be very helpful
Naomi Nosonovsky 8,431 Reputation points

2024-01-29T22:17:42.2566667+00:00

I don't remember the exact solution :( This is how the package looks like

Did you try following the last link from Yitzhak?

Answer 1

Yitzhak Khabinsky 26,586

Hi @Anonymous ,

Here we go:

Naomi Nosonovsky 8,431 Reputation points

2022-05-27T17:12:28.177+00:00

Thanks, Yitzhak. I'm trying to decide if I'm going to store XML in the table or create a few tables to store the data in relational format. Right now I only got the new xsd.
Naomi Nosonovsky 8,431 Reputation points

2022-06-02T17:13:41.14+00:00

I tried following the last example. I'm getting this error
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC02090BB at Data Flow Task, Read XML into FileContent Output [46]: Opening the file "1" for reading failed. The file was not found.
Error: 0xC0209029 at Data Flow Task, Read XML into FileContent Output [46]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Read XML into FileContent Output" failed because error code 0xC02090BB occurred, and the error row disposition on "Read XML into FileContent Output.Inputs[Import Column Input].Columns[FilePath]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

File does it say file "1" instead of the actual file path name? The only difference is what I used Unicode text and no BOM.

Answer 2

Hi @Anonymous ,

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.

Answer 3

Naomi Nosonovsky 8,431

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

Yitzhak Khabinsky 26,586 Reputation points

2022-06-02T18:28:39.92+00:00

@Anonymous ,

Please add a couple of screen shots to show us what you are doing.
Naomi Nosonovsky 8,431 Reputation points

2022-06-02T19:14:49.993+00:00

My colleague helped me to find the problem. The parameter was mapped incorrectly as output in another control flow task.

Share via

Loading XML file into XML column using SSIS

2 additional answers

Your answer