DTSX & dtsConfig: Creating your own dtsConfig as an external means to set values in your dtsx ssis package.

Purpose
This purpose of this brief tutorial is to show common components in a DTSX SSIS package (created with something like Visual Studio 2019 and SQL Server Integration Services) and how those map to a dtsConfig file. A dtsConfig file allows you to set data (like values) in the DTSX package. In a larger environment where a DTSX package may be operating on different servers, it saves considerable time by being able to externally change some of the data the DTSX package uses rather than hard-coding those changes into the DTSX package itself (which could quickly become a maintenance nightmare). In a CI/CD pipeline, where you deploy the same project to a range of different server endpoints, the dtsConfig allows you to leverage a tokenization process to dynamically change data as needed for each endpoint.

In some development environments you may not be able to use the wizard to generate a dtsConfig file. In such a case, you will need to get an idea of how a DTSX package is architected (you can open and save it with something as simple as Notepad) so, when you write (or may need to manually edit) a dtsConfig file (which you can also open and save with Notepad) you can accurately handle specific data types. That is much better than the lazy method I've seen where everything is treated as a string value. Ugh.

DTSX Package File
In Visual Studio you can open this file as code or with the designer. A DTSX package is composed as an XML document but may have binary data injected into it depending on what components you are using in SSIS. The format of the DTSX file resembles:

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
  DTS:refId="Package"
  DTS:CreationDate="1/1/0001 01:01:01 AM"
  DTS:CreationName="Microsoft.Package"
	[...]
  DTS:PackageType="5">
  <DTS:ConnectionManagers>
	[...]
  </DTS:ConnectionManagers>
  <DTS:Configurations>
	[...]
  </DTS:Configurations>
  <DTS:Variables>

<?ignore
  DataType="2" -> ValueType="Int16"
?>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{Unique GUID}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="FirstID">
      <DTS:VariableValue
        DTS:DataType="2">9</DTS:VariableValue>
    </DTS:Variable>

<?ignore
  DataType="3" -> ValueType="Int32"
?>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{Unique GUID}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="SecondID">
      <DTS:VariableValue
        DTS:DataType="3">25</DTS:VariableValue>
    </DTS:Variable>

<?ignore
  DataType="8" -> ValueType="String"
?>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{Unique GUID}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="Title">
      <DTS:VariableValue
        DTS:DataType="8">Title String</DTS:VariableValue>
    </DTS:Variable>

<?ignore
  DataType="11" -> ValueType="Boolean"
?>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{Unique GUID}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="YesOrNo">
      <DTS:VariableValue
        DTS:DataType="11">0</DTS:VariableValue>
    </DTS:Variable>

<?ignore
  DataType="13" -> ValueType="Object"
?>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{Unique GUID}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="TheCollection">
      <DTS:VariableValue
        DTS:DataSubType="ManagedSerializable"
        DTS:DataType="13">
        <SOAP-ENV:Envelope xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
          <SOAP-ENV:Body>
            <xsd:anyType id="ref-1"></xsd:anyType>
          </SOAP-ENV:Body>
        </SOAP-ENV:Envelope>
      </DTS:VariableValue>
    </DTS:Variable>

<?ignore
  DataType="17" -> ValueType="Byte"
?>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{Unique GUID}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="CurrentItem">
      <DTS:VariableValue
        DTS:DataType="17">3</DTS:VariableValue>
    </DTS:Variable>
  </DTS:Variables>

dtsConfig File
The dtsConfig file is composed as an XML document and allows you to override values in the DTSX package. In larger systems, this file is where tokenization would occur; for example, for the first configuration item instead of the value being "10" it would have a marker (or token) syntax like "_tokenName_" and that token would get replaced with an actual value. Then the DTSX package would use the actual value.

<?xml version="1.0"?>
<DTSConfiguration>
	<Configuration ConfiguredType="Property" Path="\Package.Variables[User::FirstID].Properties[Value]" ValueType="Int16">
		<ConfiguredValue>10</ConfiguredValue>
	</Configuration>
	<Configuration ConfiguredType="Property" Path="\Package.Variables[User::SecondID].Properties[Value]" ValueType="Int32">
		<ConfiguredValue>100</ConfiguredValue>
	</Configuration>
	<Configuration ConfiguredType="Property" Path="\Package.Variables[User::Title].Properties[Value]" ValueType="String">
		<ConfiguredValue>Title Value</ConfiguredValue>
	</Configuration>
	<Configuration ConfiguredType="Property" Path="\Package.Variables[User::YesOrNo].Properties[Value]" ValueType="Boolean">
		<ConfiguredValue>1</ConfiguredValue>
	</Configuration>
	<Configuration ConfiguredType="Property" Path="\Package.Variables[User::CurrentItem].Properties[Value]" ValueType="Byte">
		<ConfiguredValue>5</ConfiguredValue>
	</Configuration>
</DTSConfiguration>