XML task in SSIS

-

XML task in SSIS

XML task in SSIS
In this article we will learn about XML task in SSIS. XML task is used to validate, modify, extract or even create files in an XML format.
Drag XML task from tool box and drop on Control flow tab as shown in Figure1
clip_image002 Figure 1
Double click xml task which displays XML task editor as shown in Figure 2 which is having only two tabs. In General tab, you need to specify the connections and type of operation should be performed.
clip_image004Figure 2
General tab properties changed based on the OperationType selected. The XML operation types are as follows.
  • Validate: This option allows XML file schema validation against Document Type Definition (DTD) or XML Schema Definition (XSD) binding control documents. XML task with this option makes sure that XML file is in required format.
  • XSLT: The Extensible Stylesheet Language Transformations (XSLT) are a subset of the XML language that enables transformation of XML data.
  • XPATH: This option uses the XML Path Language and allows the extraction of sections or specific nodes from the structure of the XML document. This option will be used to extract data from XML nodes.
  • Merge: This option allows for the merging of two XML documents with the same structure. this option will be used to combine the results of two extracts from different systems into one document.
  • Diff: This option allows us to compare two XML documents to produce a third document called an XML Diffgram that contains the differences between them.
  • Patch: This option applies the results of a Diff operation to an XML document to create a new XML document.
You can specify the XML file source either through direct input or variable or file connection. This is common for all options.
Secondoperation tab specifies source type and source that contains second XML document to complete the selected operation.
Lets us understand XML task with an example which uses validates an XML using validate option
Steps to follow:
  1. Create one dummy xml file as mentioned below. You can create using any available XML editor.



    2.     col1
        col2
        col3
        col4
        col5
        col6
  2. Generate XSD/XML schema either through any generate tool or online. I have created below XSD document using online tool saved as text_xml.xsd
    3. http://www.w3.org/2001/XMLSchema"
>
 
   
     
       
         
           
             
             
             
             
             
             
           
         
       
     
   
 

  • Create a package and add XML task on the control flow design editor.
  • Open configuration configuration editor and select operation type as validate
  • Select a source type as direct input and Write the XML. You can specify even through file connection
  • In OperationResult property in Output tab specify how to capture the result of validation. Add output result to file test.txt and set OverWriteDestination property to true to allows result to be over written.
  • In SecondOperand tab you need to create file connection with XSD document which is created in Step2
  • Set validation type as XSD file to validate XML file.
    • XML task editor should be shown as displayed below after configuration completed.
    image
    If you execute the package, Task will be executed successfully and turned into Green color and writes true into text.txt file which indicates XML file contains the correct schema as defined by XSD file. If you want you can test giving wrong XML file which writes false into file

    Comments

    Post a Comment

    Popular posts from this blog

    SharePoint 2007 - Simple Task Dashboard

    -
    Image
    example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
    Read more

    MERGE transformation in SSIS

    -
    Image
    In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
    Read more