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

Sudeep's Domain

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% · 4286
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect


Submit

15  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
    1977 · 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
    1977 · 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
    958 · 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% · 4286

  • 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
    2891 · 0% · 2

  • Thanks for pointing pointing this. I will make the changes.

    commented on Jul 11 2012 4:44AM
    Sudeep Raj
    12 · 13% · 4286

  • 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
    325 · 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% · 4286

  • 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
    325 · 0% · 129

  • Could you share your XML sample here.

    commented on Feb 19 2013 2:03AM
    Sudeep Raj
    12 · 13% · 4286

  • 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
    325 · 0% · 129

  • You could mail it to me sudeep@beyondrelational.com

    commented on Feb 19 2013 10:31AM
    Sudeep Raj
    12 · 13% · 4286

  • 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
    2701 · 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% · 4286

  • 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
    2701 · 0% · 3

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]