CODEWITHSUNDEEP
c#ssisbiml
dirtyw0lf
dirtyw0lf

Reputation: 1958

BIML SsisDataTypeOverride not working

A data conversion task in ssis does not allow to reuse the existing column for easy automatic mapping to the destination.

So i coded a foreach column and evaluated the data type, adding it dynamically to the columns collection.

I noticed that the DataType and Length are required however they have no effect for ReplaceExisting=true.

Did anyone find a way to force the new data type for conversion purposes in the derived task using biml?

This does not work.

And casting the column using (DT_WSTR,length) does come out when generating SSIS package but does not change the datatype defined for that column in the derived task.

<DerivedColumns Name="der_conversions">
<Columns>
    <# 
    foreach(var column in table.Columns)
    {
        if(column.DataType == System.Data.DbType.AnsiString)
        {
    #> 
    <!-- the DataType and Length are required however they have no effect for ReplaceExisting=true -->
    <Column  ReplaceExisting="true" Name="<#=column.Name#>" DataType="String" SsisDataTypeOverride="DT_WSTR" Length="<#=column.Length#>"><#=column.Name#></Column>  
    <#  
        }//endif 
    }//endforeach
    #>
</Columns>

Upvotes: 1

Views: 662

Answers (2)

billinkc
billinkc

Reputation: 61249

A Derived Column component cannot change the data type if you have selected the Replace Existing column. In the following example, my column TABLE is a unicode string, length 255. In the first row, I explicitly change the type to a non-unicode string and observe the data type remains DT_WSTR 255. The same formula applied to a new column correctly changes the type. Had I replaced the static string of ABC with a column reference, (DT_STR, 255, 1252) [TABLE] I would see the same result - changed data type.

Derived Column Type Change

While I don't have a 2005 instance to test against, I believe this functionality did exist with the first iteration but was removed by 2012.

Upvotes: 2

dirtyw0lf
dirtyw0lf

Reputation: 1958

Ended up using the data conversion task with a dynamic rule in the oledb destination

<OleDbDestination Name="oledb_dst_<#=table.Name#>" ConnectionName="cmgr_zaprisque_dvar"  KeepIdentity="true">
                                <ExternalTableOutput Table="<#=table.Schema.Name#>.<#=table.Name#>" />
                                <Columns>
                                <# 
                                    foreach(var column in table.Columns)
                                    {
                                        if(column.DataType == System.Data.DbType.AnsiString)
                                        {
                                    #> 
                                    <!-- the DataType and Length are required however they have no effect for ReplaceExisting=true -->
                                    <Column SourceColumn="<#=column.Name#>_conv" TargetColumn="<#=column.Name#>"/> 
                                    <#  
                                        }//endif 
                                        else
                                        {#> 
                                          <Column SourceColumn="<#=column.Name#>" TargetColumn="<#=column.Name#>"/> 
                                        <# }

                                    }//endforeach
                                    #>
                                </Columns>

                                </OleDbDestination>

Upvotes: 1

Related Questions