Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS – Read XML file in Script Component as Source

Mar 26 2011 12:00AM by Sudeep Raj   

For a long time I was planning to write on this topic. So here I am. I take a example of reading XML file in Script component as source. I am using XML Document to read the XML file in my case. In case your file size is huge I would recommend you use XML Reader. I find XML Document simpler to code :). In order to use XML Document you would need to know a bit of XPATH to read the XML file. Ok so now we start on the actual file extraction.

INPUT FILE:

 

<employees>
	<employee id="1">
		<name>Jack Daniel</name>
		<age>70</age>
	</employee>
	<employee id="2">
		<name>Will Smith</name>
		<age>50</age>
	</employee>
</employees>

 

EXPECTED OUTPUT:

EmpID Name Age
1 Jack Daniel 70
2 Will Smith 50

SOLUTION:

Check this post to see how to setup the Script Component.

 

Code:

C#

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Xml;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    //Initialize XML Document to read the XML file
    private XmlDocument xDoc = new XmlDocument();

    public override void PreExecute()
    {
        base.PreExecute();
        //Provide the path to read the XML file and load the xml document
        xDoc.Load(@"C:\XML Sample\Input.xml");
    }

    public override void CreateNewOutputRows()
    {
        //Iterate through each node which has the value "employee" 
        // "//Employee" is the xpath to fetch all occurences of Employee node in the XML
        foreach (XmlNode xNode in xDoc.SelectNodes("//employee"))
        {
            //Add new row to the output buffer for each employee node in the XML file
            this.EmployeeBuffer.AddRow();

            //Assign values to the columns.

            //Read the 1st attribute of the node Employee
            this.EmployeeBuffer.EmpID= xNode.Attributes[0].Value;

            //Read the 1st Child node of the node Employee
            this.EmployeeBuffer.Name= xNode.ChildNodes[0].InnerText;

            //Read the 2nd Child node of the node Employee
            this.EmployeeBuffer.Age= xNode.ChildNodes[1].InnerText;
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }
}

VB.NET

Imports System.Data
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml

<microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute> _
Public Class ScriptMain
	Inherits UserComponent
	'Initialize XML Document to read the XML file
	Private xDoc As New XmlDocument()

	Public Overrides Sub PreExecute()
		MyBase.PreExecute()
		'Provide the path to read the XML file and load the xml document
		xDoc.Load("C:\XML Sample\Input.xml")
	End Sub

	Public Overrides Sub CreateNewOutputRows()
		'Iterate through each node which has the value "Employee" 
		' "//Employee" is the xpath to fetch all occurences of Employee node in the XML
		For Each xNode As XmlNode In xDoc.SelectNodes("//employee")
			'Add new row to the output buffer for each employee node in the XML file
			Me.EmployeeBuffer.AddRow()

			'Assign values to the columns.

			'Read the 1st attribute of the node Employee
			Me.EmployeeBuffer.EmpID = xNode.Attributes(0).Value

			'Read the 1st Child node of the node Employee
			Me.EmployeeBuffer.Name = xNode.ChildNodes(0).InnerText

			'Read the 2nd Child node of the node Employee
			Me.EmployeeBuffer.Age = xNode.ChildNodes(1).InnerText
		Next
	End Sub

	Public Overrides Sub PostExecute()
		MyBase.PostExecute()
	End Sub
End Class

Tags: SSIS, Script Component, MSBI, #BI, BRH, Script Component Source, #TSQL, #SQL Server, XML File,


Sudeep Raj
12 · 13% · 4303
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

17  Comments  

  • Sudeep,

    I have input.xml file as

    dm-tst2 DbTest dm-prod DbProd

    I want to load above Input.xml file into Script Task such that, if my package variable mRunMode="test" than mDmServer="dm-tst2" and mDmDatabase="DbTest" (this is because runMode="test" into my XML file) else mDmServer="dm-prod" and mDmDatabase="DbProd"

    -- (mRunMode, mDmServer & mDmDatabase are pkg variables)

    How to write C# code?

    Thanks You

    -Kumar

    commented on Oct 18 2011 2:31PM
    deep.apex1393
    2079 · 0% · 7
  • < Variables > < modeId runMode="test" > < dmServer >dm-tst2< /dmServer > < dmDatabase >DbTest< /dmDatabase > < /modeId > < modeId runMode="prod" > < dmServer >dm-prod< /dmServer > < dmDatabase >DbProd< /dmDatabase > < /modeId > < /Variables >

    commented on Oct 18 2011 2:35PM
    deep.apex1393
    2079 · 0% · 7
  • Hello Sandeep,

    It is very well to use Script Task instead of XML Task as it can provide dynamic behavior to your XML parsing. Thanks for sharing this information.

    I am facing problem in complex XML file. My each XML node is having many sub children and on their attribute condition i want to proceed. But I am unable to find how to proceed on conditions.

    E.g:

    'Table Fact = "Y">

     <Name>Company</Name>
    
     <Fields>
    
                <Field HasChild = "N">
    
                              <Name>Name</Name>
    
                              <Size>100</Size>
    
                              <Type>Text</Type>
    
               </Field>
    
              <Field HasChild = "Y">
    
                            <Name>Account Manager</Name>
    
                            <Size>8</Size>
    
                            <Type>Binary</Type>
    
                            <ChildTable>Employee</ChildTable>
    
            </Field>
    
     <Fields>
    

    /Table>`

    Now i want to parse on condition when attribute HasChild is "Y" then select all its Child nodes. Number of Child nodes can be varied. This Same condition follows for attribute Fact also. When it is "Y" then dynamic parsing.

    So can you just help me out with this problem.

    commented on Jan 25 2012 12:20AM
    Vikas Shrivastava
    979 · 0% · 27
  • Hi Vikash, You need to use XPATH correctly. Check for the XPATH samples in the link I provided from W3School.com.

    commented on Jan 27 2012 12:16AM
    Sudeep Raj
    12 · 13% · 4303
  • Thank you for your article, it really helped! Nevertheless i faced some problems with your example, if someone also had some problems try to change

    For Each xNode As XmlNode In xDoc.SelectNodes("//Employee") " TO For Each xNode As XmlNode In xDoc.SelectNodes("//employee")

    Only problem of case-sensitivity

    commented on Jul 11 2012 4:33AM
    tomas.hunacek
    3070 · 0% · 2
  • Thanks for pointing pointing this. I will make the changes.

    commented on Jul 11 2012 4:44AM
    Sudeep Raj
    12 · 13% · 4303
  • I can't get this to work. My script task complaints me when I try to AddRow() function, (in above script ) Script complaints "doesnot contain definition and no extension method can be found (are you missing a using directive or an assembly reference?)" .. any help shall be appreciated.

    commented on Feb 15 2013 8:03PM
    quillis131
    328 · 0% · 129
  • Hi Quillis

    Are you using the same settings in this blog with the same XML?

    commented on Feb 17 2013 9:40AM
    Sudeep Raj
    12 · 13% · 4303
  • Yes ... I am exactly following your blog. My file is large (800MB), not sure if this matters?

    commented on Feb 18 2013 9:22AM
    quillis131
    328 · 0% · 129
  • Could you share your XML sample here.

    commented on Feb 19 2013 2:03AM
    Sudeep Raj
    12 · 13% · 4303
  • I couldnt write in XML format style because of some settings on your site.... But I tried to come up with this

    parent1tag

    parent2tag tag1 closetag1 tag2 closetag2 tag3 tag4 tag5 closetag5 tag6 closetag6 closetag4 tag5 tag6 closetag6 tag7 closetag7 closetag5 closetag3 Closeparent2tag

    Parent2tag tag1 closetag1 tag2 closetag2 tag3 tag4 tag5 closetag5 tag6 closetag6 closetag4 tag5 tag6 closetag6 tag7 closetag7 closetag5 closetag3 Closeparent2tag

    closeparent1tag

    commented on Feb 19 2013 9:41AM
    quillis131
    328 · 0% · 129
  • You could mail it to me sudeep@beyondrelational.com

    commented on Feb 19 2013 10:31AM
    Sudeep Raj
    12 · 13% · 4303
  • Hello I tried your code to loop through xml but when I run it. It only retrieves the first record out of many events.

    commented on Mar 21 2013 11:15AM
    ddepass
    2844 · 0% · 3
  • How is your XML structure? Is it a simple one like I used?

    commented on Mar 22 2013 12:31AM
    Sudeep Raj
    12 · 13% · 4303
  • I have about 23 child nodes in an Event node with multiple events.

    I clicked on the post link http://beyondrelational.com/modules/2/blogs/106/posts/11119/script-componentsource-part1.aspx to show how you set up the script component but its not setup for xml. I was confused. Any Ideas Thanks Dwight

    commented on Mar 22 2013 12:53AM
    ddepass
    2844 · 0% · 3
  • Hi,

    Please can you give me idea how can I Read below file using ssis script.

    my file have multipel level.

    when I am trying use as per aboue example I am not able to read this file task is executing but no out put.

    Here I am trying to import data from \PurchaseOrder\OrderLineItem\SpecItem\Option level

    I have copyed my file source as below

    <?xml version="1.0" encoding="utf-8"?>
    <Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://www.ofdaxml.org/schema C:\PROGRA~1\HMI\XML\OFDAOrder_03_00_00.xsd" 
    xmlns="http://www.ofdaxml.org/schema" SchemaVersion="03.00.00">
      <Header>
        <Vendor>
          <Enterprise>
            <Code>www.hm.com</Code>
          </Enterprise>
          <Code>CHM</Code>
        </Vendor>
        <Language>en-US</Language>
        <Currency>INR</Currency>
      </Header>
      <PurchaseOrder>
        <Project>
          <Title>ITR110.spe</Title>
          <SpecifierFile>
            <Name>W:\HM.xml</Name>
            <SpecifierSoftware Version="104.00">HMI</SpecifierSoftware>
          </SpecifierFile>
          <BusinessSoftware>HMSV104.00</BusinessSoftware>
        </Project>
        <Header>
          <PONumber>HMSV.spe</PONumber>
          <TransactionCode>New</TransactionCode>
          <CreatedDate>2010-11-29</CreatedDate>
          <RequestedDate Type="DeliverOnOrBefore">2011-07-15</RequestedDate>
          <BillType>Dealer</BillType>
          <OrderType>99</OrderType>
          <Contract>
            <Code>CG1615</Code>
            <Description>INDIA</Description>
            <SalesVolume Currency="INR">0</SalesVolume>
          </Contract>
        </Header>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HMI</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>1</LineItemNumber>
          <Price>
            <PublishedPrice>60.00</PublishedPrice>
            <PublishedPriceExt>60.00</PublishedPriceExt>
            <EndCustomerPrice>18.00</EndCustomerPrice>
            <EndCustomerPriceExt>18.00</EndCustomerPriceExt>
            <OrderDealerPrice>15.00</OrderDealerPrice>
            <OrderDealerPriceExt>15.00</OrderDealerPriceExt>
            <DiscountCategory>FT</DiscountCategory>
            <OrderDealerDiscount>75.00</OrderDealerDiscount>
            <EndCustomerDiscount>70.00</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>FT199.</Number>
            <Description>+Cable Management Trough</Description>
            <Catalog>
              <Code>HFT</Code>
            </Catalog>
          </SpecItem>
        </OrderLineItem>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HM</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>2</LineItemNumber>
          <Price>
            <PublishedPrice>1157.00</PublishedPrice>
            <PublishedPriceExt>1157.00</PublishedPriceExt>
            <EndCustomerPrice>557.93</EndCustomerPrice>
            <EndCustomerPriceExt>557.93</EndCustomerPriceExt>
            <OrderDealerPrice>428.09</OrderDealerPrice>
            <OrderDealerPriceExt>428.09</OrderDealerPriceExt>
            <DiscountCategory>MS</DiscountCategory>
            <OrderDealerDiscount>63.00</OrderDealerDiscount>
            <EndCustomerDiscount>51.778</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>46-3620-69</Number>
            <Description>+Stg Case Std Pull 36W 20D CaseHgt 65 5/8H</Description>
            <Catalog>
              <Code>MFS</Code>
            </Catalog>
            <Option Sequence="1">
              <Code>XS</Code>
              <Description>+textured paint on smooth steel</Description>
            </Option>
            <Option Sequence="2">
              <Code>G1</Code>
              <Description>+graphite</Description>
            </Option>
            <Option Sequence="3">
              <Code>W</Code>
              <Description>+coat rod with 3 shelves (only available with T2 top)</Description>
            </Option>
            <Option Sequence="4">
              <Code>KD</Code>
              <Description>+keyed differently  black</Description>
            </Option>
            <Option Sequence="5">
              <Code>T2</Code>
              <Description>+1 1/4 -high painted metal top with squared edge</Description>
            </Option>
            <Option Sequence="6">
              <Code>B1</Code>
              <Description>+1 -high base</Description>
            </Option>
          </SpecItem>
        </OrderLineItem>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HMI</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>3</LineItemNumber>
          <Price>
            <PublishedPrice>1844.00</PublishedPrice>
            <PublishedPriceExt>1844.00</PublishedPriceExt>
            <EndCustomerPrice>922.00</EndCustomerPrice>
            <EndCustomerPriceExt>922.00</EndCustomerPriceExt>
            <OrderDealerPrice>737.60</OrderDealerPrice>
            <OrderDealerPriceExt>737.60</OrderDealerPriceExt>
            <DiscountCategory>MF</DiscountCategory>
            <OrderDealerDiscount>60</OrderDealerDiscount>
            <EndCustomerDiscount>50</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>26-3620-4</Number>
            <Description>+Lateral File Std Pull 4-High 36W 20D</Description>
            <Catalog>
              <Code>MER</Code>
            </Catalog>
            <Option Sequence="1">
              <Code>XS</Code>
              <Description>+textured paint on smooth steel</Description>
            </Option>
            <Option Sequence="2">
              <Code>G1</Code>
              <Description>+graphite</Description>
            </Option>
            <Option Sequence="3">
              <Code>T1</Code>
              <Description>+1 -high painted metal top with squared edge</Description>
            </Option>
            <Option Sequence="4">
              <Code>OH</Code>
              <Description>+overhead hinged door 26-1/4</Description>
            </Option>
            <Option Sequence="5">
              <Code>NS1</Code>
              <Description>+1 shelf total</Description>
            </Option>
            <Option Sequence="6">
              <Code>KD</Code>
              <Description>+keyed differently  black</Description>
            </Option>
            <Option Sequence="7">
              <Code>E</Code>
              <Description>+fixed front 13-1/8</Description>
            </Option>
            <Option Sequence="8">
              <Code>9R</Code>
              <Description>+side-to-side filing rail</Description>
            </Option>
            <Option Sequence="9">
              <Code>KD</Code>
              <Description>+keyed differently  black</Description>
            </Option>
            <Option Sequence="10">
              <Code>E</Code>
              <Description>+fixed front 13-1/8</Description>
            </Option>
            <Option Sequence="11">
              <Code>9R</Code>
              <Description>+side-to-side filing rail</Description>
            </Option>
            <Option Sequence="12">
              <Code>E</Code>
              <Description>+fixed front 13-1/8</Description>
            </Option>
            <Option Sequence="13">
              <Code>9R</Code>
              <Description>+side-to-side filing rail</Description>
            </Option>
            <Option Sequence="14">
              <Code>CB</Code>
              <Description>+counterweight</Description>
            </Option>
            <Option Sequence="15">
              <Code>B1</Code>
              <Description>+Base 1 in H</Description>
            </Option>
          </SpecItem>
        </OrderLineItem>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HMI</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>4</LineItemNumber>
          <Price>
            <PublishedPrice>626.00</PublishedPrice>
            <PublishedPriceExt>626.00</PublishedPriceExt>
            <EndCustomerPrice>194.06</EndCustomerPrice>
            <EndCustomerPriceExt>194.06</EndCustomerPriceExt>
            <OrderDealerPrice>162.76</OrderDealerPrice>
            <OrderDealerPriceExt>162.76</OrderDealerPriceExt>
            <DiscountCategory>UP</DiscountCategory>
            <OrderDealerDiscount>74.00</OrderDealerDiscount>
            <EndCustomerDiscount>69.00</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>LW120.20BF</Number>
            <Description>+Mobile Pedastool W-Pull 20D B/F</Description>
            <Catalog>
              <Code>HTU</Code>
            </Catalog>
            <Option Sequence="1">
              <Code>SR</Code>
              <Description>+3/4-extension roller slides on box drawers  full-extension ball bearing on file drawer
     </Description>
            </Option>
            <Option Sequence="2">
              <Code>XS</Code>
              <Description>+textured paint on smooth steel</Description>
            </Option>
            <Option Sequence="3">
              <Code>G1</Code>
              <Description>+graphite</Description>
            </Option>
            <Option Sequence="4">
              <Code>8T</Code>
              <Description>+crossing-Pr Cat 1</Description>
            </Option>
            <Option Sequence="5">
              <Code>18</Code>
              <Description>+crossing indigo</Description>
            </Option>
            <Option Sequence="6">
              <Code>H1</Code>
              <Description>+hand grip</Description>
            </Option>
            <Option Sequence="7">
              <Code>KD</Code>
              <Description>+keyed differently  black</Description>
            </Option>
            <Option Sequence="8">
              <Code>5M</Code>
              <Description>+pencil tray in box drawer  2 file converters in file drawer</Description>
            </Option>
          </SpecItem>
        </OrderLineItem>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HMI</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>5</LineItemNumber>
          <Price>
            <PublishedPrice>2485.71</PublishedPrice>
            <PublishedPriceExt>2485.71</PublishedPriceExt>
            <EndCustomerPrice>1491.43</EndCustomerPrice>
            <EndCustomerPriceExt>1491.43</EndCustomerPriceExt>
            <OrderDealerPrice>745.71</OrderDealerPrice>
            <OrderDealerPriceExt>745.71</OrderDealerPriceExt>
            <DiscountCategory>FT</DiscountCategory>
            <OrderDealerDiscount>70</OrderDealerDiscount>
            <EndCustomerDiscount>40</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>NXLR6630E1S</Number>
            <Description>+Work Surf Sq-Edge Rectangular Lam 30D 66W</Description>
            <Catalog>
              <Code>BKR</Code>
            </Catalog>
            <Option Sequence="1">
              <Code>28</Code>
              <Description>+canyon</Description>
            </Option>
            <Option Sequence="2">
              <Code>Edge</Code>
              <Description>WN</Description>
            </Option>
            <Option Sequence="3">
              <Code>Base</Code>
              <Description>black</Description>
            </Option>
          </SpecItem>
        </OrderLineItem>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HMI</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>6</LineItemNumber>
          <Price>
            <PublishedPrice>757.00</PublishedPrice>
            <PublishedPriceExt>757.00</PublishedPriceExt>
            <EndCustomerPrice>319.45</EndCustomerPrice>
            <EndCustomerPriceExt>319.45</EndCustomerPriceExt>
            <OrderDealerPrice>267.22</OrderDealerPrice>
            <OrderDealerPriceExt>267.22</OrderDealerPriceExt>
            <DiscountCategory>ZZ</DiscountCategory>
            <OrderDealerDiscount>64.70</OrderDealerDiscount>
            <EndCustomerDiscount>57.80</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>Workrite monitor arm</Number>
            <Description>Monitor arm with extended arm </Description>
            <Catalog>
              <Code>WRT</Code>
            </Catalog>
            <Option Sequence="1">
              <Code>0I</Code>
              <Description>@silver</Description>
            </Option>
          </SpecItem>
        </OrderLineItem>
        <OrderLineItem>
          <VendorRef EnterpriseRef="www.hm.com">HMI</VendorRef>
          <Status>Complete</Status>
          <Quantity>1</Quantity>
          <LineItemNumber>7</LineItemNumber>
          <Price>
            <PublishedPrice>440.00</PublishedPrice>
            <PublishedPriceExt>440.00</PublishedPriceExt>
            <EndCustomerPrice>440.00</EndCustomerPrice>
            <EndCustomerPriceExt>440.00</EndCustomerPriceExt>
            <OrderDealerPrice>308.00</OrderDealerPrice>
            <OrderDealerPriceExt>308.00</OrderDealerPriceExt>
            <DiscountCategory></DiscountCategory>
            <OrderDealerDiscount>30</OrderDealerDiscount>
            <EndCustomerDiscount>0</EndCustomerDiscount>
          </Price>
          <SpecItem>
            <Number>Installation</Number>
            <Description>Installation Charge</Description>
            <Catalog>
              <Code>CHA</Code>
            </Catalog>
          </SpecItem>
        </OrderLineItem>
      </PurchaseOrder>
    </Envelope>
    
    commented on Dec 16 2013 8:13AM
    kkhobre
    3070 · 0% · 2
  • hi Kkhobre, You need to use the XPATH to get to the specific node. Try w3School for examples and learn the basics. Its a great place to start.

    commented on Dec 19 2013 5:48AM
    Sudeep Raj
    12 · 13% · 4303

Your Comment


Sign Up or Login to post a comment.

"SSIS – Read XML file in Script Component as Source" rated 5 out of 5 by 4 readers
SSIS – Read XML file in Script Component as Source , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]