title: Programmatic Access to DTS Package Transformations
body: <p><strong>Question:</strong></p>
  
  <p>How can I read the transformations from a DTS package programatically, preferably in C# but C++, VB.NET, Java, Python, or a SQL procedure would work as well.</p>
  
  <p><strong>Status:</strong></p>
  
  <p>I currently have some code to load a DTS package from a file:</p>
  
  <pre class="lang-cs prettyprint-override"><code>using System;
  
  namespace ConceptSandbox
  {
      class Program
      {
          static void Main(string[] args)
          {
              String location = @"DTS.Old\GrindItemImport.dts";
              DTS.Package pkg = new DTS.Package();
  
              pkg.LoadFromStorageFile(location, null, null, null, null, null);
              Console.WriteLine("{0}: {1} Tasks", pkg.Name, pkg.Tasks.Count);
              for (Int32 i = 1; i &lt;= pkg.Tasks.Count; i++)
              {
                  for (Int32 j = 1; j &lt;= pkg.Tasks.Item(i).CustomTask.Properties.Count; j++)
                  {
                      Console.WriteLine("\t{0}: {1}", pkg.Tasks.Item(i).CustomTask.Properties.Item(j).Name, pkg.Tasks.Item(i).CustomTask.Properties.Item(j).Value);
                  }
              }
  
              Console.ReadKey(true);
          }
      }
  }
  </code></pre>
  
  <p>However, I cannot seem to drill down (or up) to get at the transformation. I've looked at the <a href="http://technet.microsoft.com/en-us/library/aa176212%28v=sql.80%29.aspx" rel="nofollow">DTS object model</a>, but it doesn't give much help and most of the resources I've found refer only how to dynamically create DTS/SSIS packages instead of how to read them.</p>
  
  <p><strong>Background:</strong></p>
  
  <p>A few years back, a segment of the company I work for did a huge refactor of a lot of code. Some of the data types were changed during this process and some of the checks on data type ranges were also lost. This data is then exported from this application to our servers and imported into our databases. The data flow is:</p>
  
  <pre class="lang-none prettyprint-override"><code>Data Input-&gt;CSV Export-&gt;Network-&gt;CSV Load to Temp Table-&gt;Temp to Final Table
  </code></pre>
  
  <p>The issue is that the export is now exporting variables that are outside the range of the database table's data types which causes failures when importing the data using DTS/SSIS packages.</p>
  
  <p>I need to perform an analysis of the data type the application uses and what our databases types are in order to put some scope around how much work will be necessary to fix these issues. This is where I am currently with this analysis:</p>
  
  <ul>
  <li>I can obtain the CSV filename from source</li>
  <li>I can obtain the column name in the CSV file from source</li>
  <li>I can link the column name in the CSV file to a variable name in the source file</li>
  <li>I can obtain the data type of the variable in the source file</li>
  <li>I can obtain the mappings from CSV file to the database table for SSIS packages.</li>
  <li>I <strong>cannot</strong> obtain the mappings from CSV file to the database table for DTS packages.</li>
  </ul>
  
  <p>If I knew what column in the CSV file mapped to what column in the database table, then I could use the table schema to get the type and then compare the application type to the table type to see if any action was needed for that specific file/column/table.</p>
  
  <p>Just for a point of reference, there several hundred DTS packages currently still in use (eventually I will use this same process modified to automate conversion of these DTS packages to SSIS packages as well).</p>