SSIS Package configuation alternatives-XML Storage

In a previous article , I demonstrated how to store configuration data wihin the SQL SERVER data base. In this one, I will demonstrate how to do use a XML file to store configuration data. 


We'll continue with the same example but to store data within the XML file. Although this file have dtsconfig as extension, it is not other than an XML file that respect the following schema. 


<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="DTSConfiguration">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="DTSConfigurationHeading">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="DTSConfigurationFileInfo">
                                <xs:complexType>
                                    <xs:attribute name="GeneratedBy" type="xs:string" use="required" />
                                    <xs:attribute name="GeneratedFromPackageName" type="xs:string" use="required" />
                                    <xs:attribute name="GeneratedFromPackageID" type="xs:string" use="required" />
                                    <xs:attribute name="GeneratedDate" type="xs:string" use="required" />
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
                <xs:element maxOccurs="unbounded" name="Configuration">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="ConfiguredValue" type="xs:string" />
                        </xs:sequence>
                        <xs:attribute name="ConfiguredType" type="xs:string" use="required" />
                        <xs:attribute name="Path" type="xs:string" use="required" />
                        <xs:attribute name="ValueType" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

As I said, I will continue using the package of  the previous article. 

An ADO.NET Source task that is connected to a Multicast task and a used this last one to place a Data Viewer so that we could view the data. So first let's go the package configurations in the SSIS menu

And set the configuration type to Xml configuration file. Once this is set, we go a head and define the XML file where configuration data should be stored, the file should have dtsconfig as extension.

The next step will be the selection of all properties those are object of configuration storage whose are the connection string and the SQL command in our case.

The generated XML file will look as follow:


<?xml version="1.0"?>
<DTSConfiguration>
    <DTSConfigurationHeading>
        <DTSConfigurationFileInfo GeneratedBy="DOMAIN\Administrator"
                                  GeneratedFromPackageName="Package"
                                  GeneratedFromPackageID="{12E6B6EC-F8B0-4760-AA5E-3D10AABEFCFE}"
                                  GeneratedDate="1/7/2011 5:55:33 PM"/>
    </DTSConfigurationHeading>
    <Configuration ConfiguredType="Property"
                   Path="\Package.Connections[LocalHost.AdventureWorks].Properties[ConnectionString]"
                   ValueType="String">
        <ConfiguredValue>Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True;Application Name=SSIS-Package-{69FF8434-F578-4878-957D-2A1BDD943ABA}LocalHost.AdventureWorks;</ConfiguredValue>
    </Configuration>
    <Configuration ConfiguredType="Property"
                   Path="\Package\Data Flow Task.Properties[[ADO NET Source].[SqlCommand]]"
                   ValueType="String">
        <ConfiguredValue>USE AdventureWorks SELECT  Sales.ContactCreditCard.ModifiedDate,
        Person.Contact.FirstName + '  ' +Person.Contact.LastName AS [Full name],
        Sales.CreditCard.CardNumber,CONVERT(NVARCHAR(5),Sales.CreditCard.ExpMonth)  + '/'   +
        CONVERT(NVARCHAR(5),Sales.CreditCard.ExpYear) AS [Expiration]
        FROM  Sales.ContactCreditCard
        INNER JOIN
        Sales.CreditCard ON Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID
          INNER JOIN
             Person.Contact ON Sales.ContactCreditCard.ContactID = Person.Contact.ContactID
            WHERE CardType = 'Vista'</ConfiguredValue>
        </Configuration>

</DTSConfiguration>


So, if we want to modify the connection string or the SQL command then we have to open the given XML file and modify the connection from there.