Unable to read the XML files using SSIS XML Source

Question

Post reply

Unable to read the XML files using SSIS XML Source

kashyap4007

SSC Eights!

Points: 859
More actions
January 24, 2019 at 1:45 pm

#379123

Hi
Can someone help me with why I am not able to load the attached XML file using SSIS?
When I tried to read using XML Source, I am not seeing any columns.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

I cannot see any attached files.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kashyap4007 SSC Eights! Points: 859 More actions · Answer 2

Not sure why the file did not get copied.
Below is the path for the file:
https://drive.google.com/file/d/1jyw9Q-K6RHs4wrqIl73aGG2bDBQaKTny/view?usp=sharing

I want to load many files like above that I am getting from the below link and I am having the same issue for all the files.
I am getting the files from the below-shared link.
ftp://164.64.106.6/Public/OCD/OCD%20Interface%20v1.1/core/

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

kashyap4007 - Thursday, January 24, 2019 2:02 PM
Not sure why the file did not get copied.
Below is the path for the file:
https://drive.google.com/file/d/1jyw9Q-K6RHs4wrqIl73aGG2bDBQaKTny/view?usp=sharing
I want to load many files like above that I am getting from the below link and I am having the same issue for all the files.
I am getting the files from the below-shared link.
ftp://164.64.106.6/Public/OCD/OCD%20Interface%20v1.1/core/

I tried and failed also. It seems the standard XML source component cannot handle that file format and therefore that you might have to "roll your own".
This would probably best be done using a script component source.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kashyap4007 SSC Eights! Points: 859 More actions · Answer 4

Thank you, Phil, for your time.
I got the solution in 2 ways.
1) By using XML Task Editor( Control flow task) .... Yitzhak Khabinsky helped me to get this solution.
The solution is very simple. It will be a two steps process:

The first step should be XML Task XSLT Transformation operation in the Control Flow.
The goal here is to re-shape the input XML file into a proper format without namespaces and inline XML Schema.
The second step is to use XML Source Adapter for the re-shaped XML file, etc. in the Data Flow.
Basically, a standard process to intake XML file and load it into a DB table.

2) By using the Script task... Did this as you suggested.

But the real issue here is one of the files I am planning to load is around 34 GB and I am getting the below error when I run."
Below pic shows the error.

Below is the Script task code I used.

Can someone guide me on this, please ?

kashyap4007 SSC Eights! Points: 859 More actions · Answer 5

Again Yitzhak Khabinsky helped me on this and below is the way I was able to resolve this.

Here is a streaming solution for using without XSLT 3.0
This is by using.Net Framework LINQ to XML built-in streaming functionality.
It should be able to process XML files of any size, no RAM memory involved.

Attached is the solution.

Kash