SSIS XML Task to Transform XML File Using XSLT

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

Transform XML File Using XSLT 1

The following screenshot will show you the XSLT style sheet we will use in this example.

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. Please refer to the below hyperlinks to see

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 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 Direct Input directly.

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

SSIS XML Task to Transform XML File Using XSLT 5

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

Task to Transform XML File Using XSLT 6

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

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

SSIS XML Task to Transform XML File Using XSLT 7

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 must 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 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 Direct Input.

In this example, we use 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 have already created the File Connection Manager, please select the same. We haven’t created any here, so we select <New Connection..>.

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.

XML Task to Transform XML File Using XSLT 12

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 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 on 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 have already created the File Connection Manager before, please select the created one. We haven’t created any connection Manager before, so we select <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 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.