XML Source Task in SSIS
XML Source Task in SSIS
Suppose we want use a XML file as a data source for further processing on it’s data.
SSIS XML source is basically used when we want to read the data from XML files.
First you will have to create a SSIS Project. You can refer post ‘How to Create SSIS Project?‘ to create this.
1. Now in the project, drag and drop the ‘Data Flow Task’ into control flow tab and double click on it. It will redirect you to Data Flow Tab.
2. Drag and Drop the ‘XML Source’ from SSIS toolbox and double click on it. Below window will appear-
3. Now browse the xml file and click on ‘Generate XSD’.
What is XSD?
XSD (XML Schema Definition) is a W3C recommendation that defines the way to utilize the elements in an XML file.It specifies how to formally describe the elements in an Extensible Markup Language (XML) file.XSD can also be used for generating XML documents that can be treated as programming objects. In addition, a variety of XML processing tools can also generate human readable documentation, which makes it easier to understand complex XML documents.
Below window will open to save the XSD file. Click on Save.
4. Now click on ‘Columns’, it will show the below Warning Message which is for length of the columns and System will automatically use DT_WSTR (lenght -255).
If you don’t want to use it, in this case you need to edit XSD file manually. In this case we are accepting the warning message and click on OK.
5. After accepting warning message, columns will be available as below. Click on ‘OK’.
6. Now drag and drop a ‘Derived Column’ to preview the data and connect it with XML source using data path.
7. Now enable the data viewer and execute the package. Results will appear as below-
0 comments:
Post a Comment