SSIS XML Task to Transform XML File Using XSLT

How to use SSIS XML Task to Transform XML File Using XSLT with example?.  The below screenshot shows you the data inside that Sample.xml file, and our task is to Transform this XML file.

SSIS XML Task to Transform XML File Using XSLT 1

The following screenshot will show you the XSLT style sheet that we are going to use in this example

SSIS XML Task to Transform XML File Using XSLT 2

SSIS XML Task to Transform XML File Using XSLT

To transform the XML File, First Drag and drop the XML Task into the Control Flow region and rename it as SSIS XML Task to Transform XML File Using XSLT

SSIS XML Task to Transform XML File Using XSLT 3

Double click on it will open the XML Task Editor to configure it.

In this SSIS example, We are transforming the XML file, so please change the operationType property to XSLT

SSIS XML Task to Transform XML File Using XSLT 4

Let us configure the Source Connection for SSIS XML Task to Transform XML File using XSLT by selecting the SourceType property.

  • If you are using the XML file present in the local file system, select the File Connection.
  • If you stored the Source Connection in the Variable, select the Source as Variable and select the variable name.
  • And, If you want to input the XML data, select the Direct Input directly.

In this example, we are using the above specified Sample.xml file as source so, we are selecting the SourceType property as File Connection.

SSIS XML Task to Transform XML File Using XSLT 5

If you already created the File Connection Manager, please select the same. Here, We haven’t created any, so we are selecting <New Connection..>.

SSIS XML Task to Transform XML File Using XSLT 6

Once you click on the <New Connection..> option, File Connection Manager Editor will open to configure it. Here, we are validating the existing XML file, so selecting the Existing File option from the Usage Type.

Click on the Browse button to select the Existing File from our file system.

SSIS XML Task to Transform XML File Using XSLT 7

From the below, you can see that we selected the Sample.xml file inside the Destination Folder.

SSIS XML Task to Transform XML File Using XSLT 8

Now we have to configure the Second Operand Type (please select the XSLT style sheet). So, Please select the SecondOperandType property for SSIS XML Task to Transform XML File Using XSLT.

  • If the XSLT file is in the local file system, select the File Connection.
  • If you stored the XSLT file in the Variable, select the Variable and select the variable name.
  • And, If you want to input the XSLT data directly, select the Direct Input.

In this example, we are using the above-specified Sample.xsl file as a style sheet. So, we are selecting the SecondOperandType property as a File Connection.

SSIS XML Task to Transform XML File Using XSLT 9

If you already created the File Connection Manager, please select the same. Here, We haven’t created any, so we are selecting <New Connection..>.

SSIS XML Task to Transform XML File Using XSLT 10

From the below, you can observe that we selected the Sample.xsl file inside the Destination Folder

SSIS XML Task to Transform XML File Using XSLT 11

SaveOperationReslut: This property has True and False options. If we set this property to TRUE, XML Task Result will save. Otherwise, it won’t save the output. In this example, we want to store the transformed XML file. So, change the property from default False to TRUE

SSIS XML Task to Transform XML File Using XSLT 12

Now we have to configure the Output, so please select the OperationResult property.

  • If you want to store the Output in the local file system, select the File Connection.
  • To store the Output in the Variable, select the Variable option, and select the variable name.

In this example, we want to store the XML Task result in our local hard drive. So, we are selecting the DestinationType property as File Connection.

SSIS XML Task to Transform XML File Using XSLT 13

If you already created the File Connection Manager before, please select the created one. Here, We haven’t created any connection Manager before so, We are selecting <New Connection..>.

SSIS XML Task to Transform XML File Using XSLT 14

From the below screenshot, we selected the test.htm file inside the Destination Folder. It means we will store the transformed XML file into an HTML file of extension htm.

SSIS XML Task to Transform XML File Using XSLT 15

OverwriteDetination: This property has the True and False options. If we set this property to true, XML Task overwrites the existing files in the Destination path.

SSIS XML Task to Transform XML File Using XSLT 16

From the below image you can observe that we set the OverwriteDetination property to TRUE

SSIS XML Task to Transform XML File Using XSLT 17

Let’s run the SSIS XML Task to Transform XML File using the XSLT package and see whether we transformed our Sample.xml file correctly or not.

SSIS XML Task to Transform XML File Using XSLT 18

From the above screenshot, you can observe that our package runs successfully. Let us open the test.htm file and see the result.

SSIS XML Task to Transform XML File Using XSLT 19

Comments are closed.