BIML GetDropAndCreateDdl creating incorrect lengths for datatypes
<p>When I use GetDropAndCreateDdl to generate CREATE scripts for tables, I get datatypes for columns that are different than what the datatypes actually are. </p>

<p>This results in a package validation error that "the error output has properties that do not match the properties of its corresponding data source column" and a validation status of "VS_NEEDSNEWMETADATA". </p>

<p>If I right-click the connection source, select Show Advanced Editor, and take a look at the Column Mappings, I can see that [Column1] in the Available External Columns list has a different Length than the datatype that was generated in the GetDropAndCreateDdl. I can delete and re-create the metadata mappings, but that is not a viable solution since there are many dataflow tasks.</p>

<p>How do I get GetDropAndCreateDdl to create the correct datatypes with correct lengths?</p>

<p>I am using ImportDB to get the list of tables, metadata, etc.</p>

<p><strong>Environment.biml</strong></p>

<pre><code>&lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
    &lt;Connections&gt;
        &lt;OdbcConnection Name="OdbcSrc_DV" ConnectionString="Dsn=Source-32bit-test;" /&gt;
        &lt;OleDbConnection Name="OleDbDst_Staging" ConnectionString="Provider=SQLNCLI11;Server=SQL-DEV;Initial Catalog=Source_Staging;Integrated Security=SSPI;" /&gt;
    &lt;/Connections&gt;
    &lt;Databases&gt;
        &lt;Database Name="Source" ConnectionName="OdbcSrc_DV" /&gt;
        &lt;Database Name="Source_Staging" ConnectionName="OleDbDst_Staging" /&gt;
    &lt;/Databases&gt;
    &lt;Schemas&gt;
        &lt;Schema Name="dbo" DatabaseName="Source" /&gt;
        &lt;Schema Name="dbo" DatabaseName="Source_Staging" /&gt;
    &lt;/Schemas&gt;
&lt;/Biml&gt;
</code></pre>

<p><strong>CreateTableMetadata.biml</strong></p>

<pre><code>&lt;#@ import namespace="System.Data" #&gt;
&lt;#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement"  #&gt;
&lt;#
var sourceConnection = RootNode.DbConnections["OdbcSrc_DV"];
var importResult = sourceConnection.ImportDB("", "", ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);

var tableNamesToImport = new List&lt;string&gt;() { "Test_Table" };
#&gt;
&lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
  &lt;Tables&gt;

    &lt;# foreach (var table in importResult.TableNodes.Where(item =&gt; tableNamesToImport.Contains(item.Name)).OrderBy(item =&gt; item.Name)) { #&gt;

    &lt;Table Name="&lt;#=table.Name#&gt;" SchemaName="Source.dbo"&gt;
      &lt;Columns&gt;
            &lt;#=table.Columns.GetBiml()#&gt;
      &lt;/Columns&gt;
      &lt;Annotations&gt;
        &lt;Annotation AnnotationType="Tag" Tag="SourceSchemaQualifiedName"&gt;&lt;#=table.SchemaQualifiedName#&gt;&lt;/Annotation&gt;
      &lt;/Annotations&gt;
    &lt;/Table&gt;
    &lt;# } #&gt;
  &lt;/Tables&gt;
&lt;/Biml&gt;
</code></pre>

<p><strong>DeployTargetTables.biml</strong></p>

<pre><code>&lt;#@ template tier="2" #&gt;
&lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
  &lt;Packages&gt;
    &lt;Package Name="MasterTableDeploy" ConstraintMode="Parallel"&gt;
      &lt;Tasks&gt;
        &lt;# foreach (var table in RootNode.Tables) { #&gt;

        &lt;ExecuteSQL Name="SQL CREATE &lt;#=table.Name#&gt;" ConnectionName="OleDbDst_Staging"&gt;
          &lt;DirectInput&gt;&lt;#=table.GetDropAndCreateDdl()#&gt;&lt;/DirectInput&gt;
        &lt;/ExecuteSQL&gt;
        &lt;# } #&gt;
      &lt;/Tasks&gt;
    &lt;/Package&gt;
  &lt;/Packages&gt;
&lt;/Biml&gt;
</code></pre>

<p><strong>CreateLoadPackages.biml</strong></p>

<pre><code>&lt;#@ template tier="2" #&gt;
&lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
  &lt;Packages&gt;
    &lt;Package Name="Copy Data" ConstraintMode="Parallel"&gt;

      &lt;Tasks&gt;
        &lt;# foreach (var table in RootNode.Tables) { #&gt;
        &lt;ExecuteSQL Name="SQL TRUNCATE &lt;#=table.Name#&gt;" ConnectionName="OleDbDst_Staging"&gt;
          &lt;DirectInput&gt;TRUNCATE TABLE &lt;#=table.Name#&gt;&lt;/DirectInput&gt;
        &lt;/ExecuteSQL&gt;
        &lt;Dataflow Name="DFT LOAD &lt;#=table.Schema.Name#&gt;_&lt;#=table.Name#&gt;"&gt;
            &lt;PrecedenceConstraints&gt;
                &lt;Inputs&gt;
                    &lt;Input OutputPathName="SQL TRUNCATE &lt;#=table.Name#&gt;.Output" /&gt;
                &lt;/Inputs&gt;
            &lt;/PrecedenceConstraints&gt;
          &lt;Transformations&gt;

              &lt;OdbcSource Name="ODBC_SRC &lt;#=table.Name#&gt;" Connection="OdbcSrc_DV"&gt;
              &lt;DirectInput&gt;SELECT &lt;#=table.GetColumnList()#&gt; FROM &lt;#=table.GetTag("SourceSchemaQualifiedName")#&gt;&lt;/DirectInput&gt;
              &lt;/OdbcSource&gt;

            &lt;OleDbDestination Name="ODBC_DST &lt;#=table.Name#&gt;" ConnectionName="OleDbDst_Staging"&gt;
              &lt;TableOutput TableName="&lt;#=table.ScopedName#&gt;" /&gt;
            &lt;/OleDbDestination&gt;
          &lt;/Transformations&gt;
        &lt;/Dataflow&gt;
          &lt;# } #&gt;
      &lt;/Tasks&gt;
    &lt;/Package&gt;
  &lt;/Packages&gt;
&lt;/Biml&gt;
</code></pre>

<p>Here is the script that gets created from the ODBC source - </p>

<p><a href="https://i.imgur.com/kpbtelW.png" rel="nofollow noreferrer"><img src="https://i.imgur.com/kpbtelW.png" alt="enter image description here"></a></p>

<p>And here are the results from INFORMATION_SCHEMA.COLUMNS for the same table - </p>

<p><a href="https://i.imgur.com/VlnT3Lw.png" rel="nofollow noreferrer"><img src="https://i.imgur.com/VlnT3Lw.png" alt="enter image description here"></a></p>