SERVER HOIT ASIA

Showing posts with label XML. Show all posts

[2017-Aug-08] Support for XML structured files as a data source has been available in Excel, then was introduced into the Power Query data extract engine of the same office application which later migrated into the Power BI data modeling tool.

My recent experience working with XML data files at the last Toronto Open Data user group meeting - https://www.meetup.com/opentoronto/events/240911274/ challenged me to look at XML files at a different angle.

A request was made to analyze Toronto Lobbyist Registry Open Data Set with a very common scenario for an XML data file to hold parent (or master) node elements along with a subset of children (or nested nodes).

Here is an example of this Toronto Lobbyist Registry open XML data file:

And here is how this file looked in the Power BI Query Editor:

Basically, the ROW parent node had a following list of elements:
- SMNumber
- Status
- Type
- SubjectMatter
- Particulars
- InitialApprovalDate
- EffectiveDate
- ProposedStartDate
- ProposedEndDate

and several sub-nodes which are shown in Power BI query editor as tables:
- Registrant
- Communications
- etc.

My challenge was to build a data model that would enable these main dataset elements analysis along with its subset data elements. It wasn't that intuitive but eventually proved that Power BI is a very decent data modeling environment.

So, first, I made my parent node as a base table with SMNumber as its Primary Key (PK).
Then for each of the sub-node datasets:
1) I had to create a replica of the base table; let's take Communications table for example.
2) Then I removed every column except for the actual column with the Communications table data and I also left the SMNumber, which now had become a Foreign Key (FK) to my base table PK.

3) Then expanded Communications table columns and gave a proper name to a new table within my data model, which now showed all the evidence of a potential One-to-Many relationship between the base and this new table:

After repeating steps 1-2-3 for the other sub-nodes on my XML sourced main table I was able then to create a stable data model to accommodate my future reporting visualizations and data discovery:

One single XML file with parent and children nodes had been transformed into multiple tables, where Power BI took care of empty sub-node elements (please see the null value from the 2nd step of my child node transformation). In addition to that, a referential integrity between tables was in place and I could start working on my visualizations.

I understand that XML data source for Power BI is just a very small member of the family of other supported data sources, and they keep adding new more complicated data resources to stream your data analytics from. However, it's just another proof of how many blades this Power BI Swiss knife can have :)

Happy data adventures!

Case
A while ago I did a post on validating XML files in SSIS with nested XSD files. The out of the box XML Task doesn't honor nested XSD files with an (include or import). The Script Task workaround is simple, but since I use it a lot I decided to make a task for it.

Solution
The XML Validation Task allows you to specify the XML and XSD filepaths with a Connection Manager or string variable. After that it will either succeed or fail with an error message describing what's wrong with the XML file.

XML Validation Task V0.1

Please email me (address is under the Help button) bugs and feature requests for this task.
Items to address:

More validation to make it really monkey proof
Different icon

Thinking about:

Providing multiple XSD's (but how many?)
Providing XML and/or XSD content (not path) via string variable
Option to throw warning instead of error

Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008, 2012, 2014 and 2016 version on the download page.

V0.1 Initial version for 2008 to 2016

V0.2 ? (Leave a comment)

Installation
The installer registers the DLL in the GAC and copies it to the task folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\). After that you have to restart BIDS because it caches the GAC on startup. Restarting SQL Server Data Tools is not necessary.

Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom task.

Case
I have an XML file which I want to validate against an XSD file before processing it. However my XSD contains an include (or import) tag and the XML Task will not validate it and throws an error about certain parts that are not declared.

How can I validate an XML file against an XSD with an include (or import) tag?

Solution
Although the XML source component honors includes (and imports), the XML task doesn't. See(/vote for) this Microsoft Connect feedback.

You can use a Script Task to validate your XML file against the XSD file. For this example I use three files: movies.xml, movies2.xsd and movies2_include.xsd.

<?xml version="1.0"?>
<movies>
 <movie id="123">
  <title>Pulp Fiction</title>
  <year>1994</year>
  <rating>8.9</rating>
 </movie>
 <movie id="124">
  <title>Memento</title>
  <year>2000</year>
  <rating>8.6</rating>
 </movie>
 <movie id="124">
  <title>The Usual Suspects</title>
  <year>2000</year>
  <rating>8.6</rating>
 </movie>
</movies>

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:include schemaLocation="movies2_include.xsd"/>

  <xs:element name="movies">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="movie">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="title" type="LimitedString" />
              <xs:element minOccurs="0" name="year" type="xs:unsignedShort" />
              <xs:element minOccurs="0" name="rating" type="xs:decimal" />
            </xs:sequence>
            <xs:attribute name="id" type="xs:unsignedByte" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:simpleType name="LimitedString"> 
    <xs:restriction base="xs:string"> 
      <xs:maxLength value="50" /> 
    </xs:restriction> 
  </xs:simpleType> 
</xs:schema>

1) New File Connection
I will use Connection Managers in this example to make things easier to configure. Feel free to use something else such as variables. Right click in the Connection Managers pane and select "New File Connection...".

New File Connection for XML and XSD file

Select the xml file in the File Connection Manager Editor and repeat these steps for the XSD file (movies2.xsd) so that you have two Connection Managers named movies.xml and movies2.xsd. You don't have to create a Connection Manager for the include xsd, because it's included by code.

File Connection Manager Editor

2) Script Task
Add an Script Task to your Control Flow and give it a suitable name. You can connect it to your Data Flow Task that processes the XML file.

Script Task to validate XML

3) The script
Edit the Script Task and choose your Script Language, hit the edit button to edit the script and copy the code from the main method below to your main method and add the import/include row.

// C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Xml;   // Added
#endregion

namespace ST_d2a4d346c17d488da0cd327617293c52
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // create object for the XSD file that will be used
            // for validating the XML file. Use the Connection
            // Manager to get the path from the XSD file.
            XmlReaderSettings xmlrs = new XmlReaderSettings();
            xmlrs.ValidationType = ValidationType.Schema;
            xmlrs.Schemas.Add(null, Dts.Connections["movies2.xsd"].ConnectionString);
            xmlrs.Schemas.Compile();

            // Try reading the XML file using the XSD. Use the
            // Connection Manager to get the path from the XML file
            try
            {
                XmlReader xmlr = XmlReader.Create(Dts.Connections["movies.xml"].ConnectionString, xmlrs);
                while (xmlr.Read())
                {
                }
                // The XML file was succesfully read.
                
                // Close XML file
                xmlr.Close();

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                // Validation failed, fire error event with errormessage
                Dts.Events.FireError(-1, "Validate XML", "Validation error: " + ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        #region ScriptResults declaration
        /// 
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// 

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

or VB.net

' VB.Net Code
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml      ' Added
#End Region

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        ' create object for the XSD file that will be used
        ' for validating the XML file. Use the Connection
        ' Manager to get the path from the XSD file.
        Dim xmlrs As New XmlReaderSettings()
        xmlrs.ValidationType = ValidationType.Schema
        xmlrs.Schemas.Add(Nothing, Dts.Connections("movies2.xsd").ConnectionString)
        xmlrs.Schemas.Compile()

        ' Try reading the XML file using the XSD. Use the
        ' Connection Manager to get the path from the XML file
        Try
            Dim xmlr As XmlReader = XmlReader.Create(Dts.Connections("movies.xml").ConnectionString, xmlrs)
            While xmlr.Read()
            End While
            ' The XML file was succesfully read.

            ' Close XML file
            xmlr.Close()

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            ' Validation failed, fire error event with errormessage
            Dts.Events.FireError(-1, "Validate XML", "Validation error: " + ex.Message, String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

Note: For those using SSIS 2005: use the taskresult row from 2005 that can be found in your main method and replace the followin lines: C# line 34 & 40 / VB.Net line 31 & 35.

4) Testing
Run the package. Change something in the XML file to make it fail (a movie title longer than 50 chars).

Test result

Case
I have an XML file which I want to validate before processing it. How can I validate an XML file against an XSD file with SSIS?

Solution
There are two solutions in SSIS for validating an XML file.
A) XML Task, but the big downside is that it can't validate against XSD files with import or include tags. See(/vote for) this Microsoft Connect feedback.
B) Script Task, but that requires some .Net knowledge.

In this post I will elaborate the XML Task solution and in the next post I will workout the Script Task solution.

XML Task
For this example I use two files: movies.xml and movies.xsd

<?xml version="1.0"?>
<movies>
 <movie id="123">
  <title>Pulp Fiction</title>
  <year>1994</year>
  <rating>8.9</rating>
 </movie>
 <movie id="124">
  <title>Memento</title>
  <year>2000</year>
  <rating>8.6</rating>
 </movie>
 <movie id="124">
  <title>The Usual Suspects</title>
  <year>2000</year>
  <rating>8.6</rating>
 </movie>
</movies>

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:simpleType name="LimitedString"> 
    <xs:restriction base="xs:string"> 
      <xs:maxLength value="50" /> 
    </xs:restriction> 
  </xs:simpleType> 

  <xs:element name="movies">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="movie">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="title" type="LimitedString" />
              <xs:element minOccurs="0" name="year" type="xs:unsignedShort" />
              <xs:element minOccurs="0" name="rating" type="xs:decimal" />
            </xs:sequence>
            <xs:attribute name="id" type="xs:unsignedByte" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

New File Connection for XML and XSD file

Select the xml file in the File Connection Manager Editor and repeat these steps for the XSD file so that you have two Connection Managers named movies.xml and movies.xsd

File Connection Manager Editor

2) XML Task
Add an XML Task to your Control Flow and give it a suitable name. You can connect it to your Data Flow Task that processes the XML file.

XML Task

3) XML Task editor
Edit the XML Task and select the XML Connection Manager as input and the XSD Connection Manager as Second Operand. OperationType should be Validate and ValidationType should be XSD.

Set XML Task editor for validation

4) Testing
Run the package. Change something in the XML file to make it fail (a movie title longer than 50 chars).

The result

Note: If you try to validate against an XSD with an include or import, you will get an error that certain parts are not declared. The next post will be about validating an XML file against an XSD file with a Script Task.

Case
I want a Foreach Folder Enumerator, but the Foreach Loop component only loops through files.

Solution
Unfortunately this isn't supported by the standard Foreach Loop component, but there are a couple of workarounds:
- Use an open source component Directory Foreach Enumerator or my own Custom Foreach Folder Enumerator
- Use a Script task to accomplish this.

This example uses the Script task and a Foreach Loop to enumerate through the folders. The Script tasks generates a list of Directories and the Foreach Loop loops through that list.

1) Control Flow
Drag a Script task and a Foreach Loop container to the Control Flow like the image below.

Control Flow

2) Variables
Create three string variables:

startlocation that indicates the parent folder wherein we gonna search for subfolders. Fill it with a path like "d:\foreachfoldertest\".
xmldoc which we gonna use for communicating between the Script task and the Foreach Loop.
folder which is used for the variable mapping in the Foreach Loop container.

Varibles (right click in Control Flow)

3) Script Task
Select the startlocation as a readonly variable (we only read the path inside it) and select xmldoc as a readwritevariable (an xml string will be stored in it).

Variables

4) The Script
Copy the following script to your Script task

// C# code 
// This script loops through a folder
// and adds all subfolders to an xml string
using System;
using System.Data;
using System.IO;        // Added
using System.Xml;       // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5da96344f1c4411ab56207579f2e5e91.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Get all subfolders within the parent folder
            string[] subFolders = Directory.GetDirectories(Dts.Variables["User::startlocation"].Value.ToString());

            // Create variables for the XML string
            XmlDocument xmldoc = new XmlDocument();
            XmlElement xmlRootElem;
            XmlElement xmlChildElem;
            XmlText xmltext;

            // Add the root element: <ROOT>
            xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

            // Variable for directory information
            DirectoryInfo directoryInfo;

            // Loop through the parent folder
            foreach (string currentFolder in subFolders)
            {
                // Fill directoryInfo variable with folder information
                directoryInfo = new DirectoryInfo(currentFolder);

                // Create the child element that contains the path:
                // <Folder>d:\foreachfoldertest\subfolder1\</Folder>
                xmlChildElem = xmldoc.CreateElement("", "Folder", "");
                xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);
                xmlChildElem.AppendChild(xmltext);

                // Add the child element to the root element
                xmlRootElem.AppendChild(xmlChildElem);
            }
            // Add the root element to the xml document
            xmldoc.AppendChild(xmlRootElem);
            
            // Store the xml in the SSIS variable
            Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

This script will result in:
<ROOT>
  <Folder>d:\foreachfoldertest\subfolder1</Folder>
  <Folder>d:\foreachfoldertest\subfolder2</Folder>
  <Folder>d:\foreachfoldertest\subfolder3</Folder>
</ROOT>

5) Foreach Loop Container
Edit the Foreach Loop Container and change the enumerator on the Collection tab to Foreach NodeList Enumerator. This means that it will loop through an xml string. Change the DocumentSourceType to Variable and select the xmldoc variable as the DocumentSource. This is the variable that contains the xml string from the Script task. Last... add the following string to the OuterXPathString: "/ROOT/*" (without the quotes). This means that it's gonna look within the root element.

Foreach NodeList Enumerator

6) Variable Mappings
Map the variable folder to Index 0. This will store the path in this variable.

Variable Mappings

7) Test the result
Now you can add your own tasks to the Foreach Loop container and use the variable folder to get the path. I added a simple Script Task for testing that shows the path in a MessageBox.

Example with MessageBox

8) More advanced script
You can even add a Traverse subfolders option with this recursive method.

// C# code
// This script loops through a folder
// and adds all subfolders to an xml string
using System;
using System.Data;
using System.IO;        // Added
using System.Xml;       // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_5da96344f1c4411ab56207579f2e5e91.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        // Variables for the xml string
        private XmlDocument xmldoc;
        private XmlElement xmlRootElem;

        public void Main()
        {
            // Inialize XMLdoc
            xmldoc = new XmlDocument();

            // Add the root element: <ROOT>
            xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

            // Add Subfolders as Child elements to the root element
            GetSubFolders(Dts.Variables["User::startlocation"].Value.ToString());

            // Add root element to XMLdoc
            xmldoc.AppendChild(xmlRootElem);

            // Fill SSIS variable with XMLdoc
            Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        // Recursive method that loops through subfolders
        private void GetSubFolders(String parentFolder)
        {
            // Get subfolders of the parent folder
            string[] subFolders = Directory.GetDirectories(parentFolder);

            // XML child element
            XmlElement xmlChildElem;
            XmlText xmltext;

            // Variable for file information
            DirectoryInfo directoryInfo;

            // Loop through subfolders
            foreach (string currentFolder in subFolders)
            {
                // Fill fileInfo variable with file information
                directoryInfo = new DirectoryInfo(currentFolder);

                // Create child element "Folder":
                // <Folder>d:\foreachfoldertest\subfolder1\</Folder>
                xmlChildElem = xmldoc.CreateElement("", "Folder", "");
                xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);
                xmlChildElem.AppendChild(xmltext);
                
                // Add child element to root element
                xmlRootElem.AppendChild(xmlChildElem);

                // Recursive call
                GetSubFolders(directoryInfo.FullName);
            }
        }
    }
}

Note: there are more options to accomplish the communication between the Script Task and the Foreach Loop container, but I wanted you to show an infrequently used option of the Foreach Loop container. Let me know what your solution was.

Case
A few month ago I had a job to add some adjustments to a SSIS project, but they lost the Visual Studio project file. So I created a empty project and started adding all the packages (over a 150) from SQL Server to the project one by one. After five packages I realised this could take ages. A while ago I created a package to upload all Visual Studio project files to SQL Server and thought I can do that vica versa!

Solution
I created a package with a single Script Task and used the SSIS API to download all packages to the Visual Studio SSIS project folder. After that I edited the Visual Studio SSIS project file (.dtproj, an XML file) to add the packages to the project itself.

// C# code
// Script to download packages from a SQL Server and add them
// to a Visual Studio SSIS Project file. The script assumes
// that the package name and package object name are equal.
using System;
using System.Data;
using System.Xml; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_fe8263e907c94504a4c41974a46fc623.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            /////////////////////////////////////////
            // The Project file and folder
            String ProjectFilePath = @"d:\Integration Services Project\Integration Services Project.dtproj";
            String ProjectFolderPath = ProjectFilePath.Substring(0,ProjectFilePath.LastIndexOf(@"\")+1);

            // The SQL Server
            String SSISServer = "Name_Of_Your_Server";

            /////////////////////////////////////////
            // Variable that contains the Visual Studio SSIS Project file, an XML document     
            XmlDocument doc = new XmlDocument();
            doc.Load(ProjectFilePath);

            // Get DTSPackages to add a package
            XmlNode DTSPackages = doc.SelectSingleNode("/Project/DTSPackages");

            /////////////////////////////////////////
            // Set application context
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

            // Create a package variable to temporary store the server packages
            Package serverPackage;

            /////////////////////////////////////////
            // Loop through packages and folders in the root: / (or change the path for your folder)
            foreach (PackageInfo serverPackageInfo in app.GetPackageInfos("/", SSISServer, null, null))
            {
                // Get only the packages. You could make a recursive function to loop through all folders
                if (serverPackageInfo.Flags.ToString().ToLower().Equals("package"))
                {
                    /////////////////////////////////////////
                    // Fill the package variable with a server package
                    serverPackage = app.LoadFromSqlServer(serverPackageInfo.Folder + serverPackageInfo.Name, SSISServer, null, null, null);

                    // Save the package to xml (.dtsx) in the project folder.
                    app.SaveToXml(ProjectFolderPath + serverPackageInfo.Name + ".dtsx", serverPackage, null);

                    /////////////////////////////////////////
                    // Dirty/lazy check to see if the package already exists in the project file (can't add them twice)
                    bool fireAgain = true;
                    if (DTSPackages.InnerXml.Contains(serverPackage.Name + ".dtsx"))
                    {
                        Dts.Events.FireInformation(0, "Download package", "Existing local package had been overwritten by Server package", string.Empty, 0, ref fireAgain);
                    }
                    else
                    {
                        /////////////////////////////////////////
                        // Add package to project xml file
                        // <dtspackage formatversion="3"></dtspackage>
                        XmlNode newDtsPackageNode = doc.CreateNode(XmlNodeType.Element, "DtsPackage", "");
                        XmlAttribute newDtsPackageAttribute = doc.CreateAttribute("FormatVersion");
                        newDtsPackageAttribute.Value = "3";
                        newDtsPackageNode.Attributes.Append(newDtsPackageAttribute);

                        // <name>Datastaging.dtsx</name>
                        XmlNode newDtsPackageNameNode = doc.CreateNode(XmlNodeType.Element, "Name", "");
                        newDtsPackageNameNode.InnerText = serverPackage.Name + ".dtsx";

                        // <fullpath>Datastaging.dtsx</fullpath>
                        XmlNode newDtsPackageFullPathNode = doc.CreateNode(XmlNodeType.Element, "FullPath", "");
                        newDtsPackageFullPathNode.InnerText = serverPackage.Name + ".dtsx";

                        // <references />
                        XmlNode newDtsPackageReferencesNode = doc.CreateNode(XmlNodeType.Element, "References", "");

                        // Add child nodes to <dtspackage>
                        newDtsPackageNode.AppendChild(newDtsPackageNameNode);
                        newDtsPackageNode.AppendChild(newDtsPackageFullPathNode);
                        newDtsPackageNode.AppendChild(newDtsPackageReferencesNode);

                        // Add <dtspackage> to <dtspackages>
                        DTSPackages.AppendChild(newDtsPackageNode);
                        Dts.Events.FireInformation(0, "Download package", "Package from server has been added to the project.", string.Empty, 0, ref fireAgain);
                    }
                }
            }
            /////////////////////////////////////////
            // Save project
            doc.Save(ProjectFilePath);
           
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

I hard coded the project file and server name, but you can change that by using variables and configure those.

Latest News

e-book: Windows Virtual Desktop Migration Guide for Remote Desktop Services

Main Menu

You May Like

Follow Us!

Popular Posts

Trend

Bài mói nhất

Copyright Text