Search code examples
ssisbiml

BIML: Issues about Datatype-Handling on ODBC-Source Columns with varchar > 255

I'm just getting into BIML and have written some Scripts to creat a few DTSX-Packages. In general the most things are working. But one thing makes me crazy.

I have an ODBC-Source (PostgreSQL). From there I'm getting data out of a table using an ODBC-Source. The table has a text-Column (Name of the column is "description"). I cast this column to varchar(4000) in the query in the ODBC-Source (I know that there will be truncation, but it's ok). If I do this manually in Visual Studio the Advanced Editor of the ODBC-Source is showing "Unicode string [DT_WSTR]" with a Length of 4000 both for the External and the Output-Column. So there everything is fine. But if I do the same things with BIML and generate the SSIS-Package the External-Column will still say "Unicode string [DT_WSTR]" with a Length of 4000, but the Output-Column is telling "Unicode text stream [DT_NTEXT]". So the mapping done by BIML differs from the Mapping done by SSIS (manually). This is causing two things (warnings):

  1. A Warning that metadata has changed and should be synced
  2. And a Warning that the Source uses LOB-Columns and is set to Row by Row-Fetch..

Both warnings are not cool. But the second one also causes a drasticaly degredation in Performance! If I set the cast to varchar(255) the Mapping is fine (External- and Output-Column is then "Unicode string [DT_WSTR]" with a Length of 255). But as soon as I go higher, like varchar(256) it's again treated as [DT_NTEXT] in the Output.

Is there anything I can do about this? I invested days in the Evaluation of BIML and find many things an increase in Quality of Life, but this issue is killing it. It defeats the purpose of BIML if I have to correct the Errors of BIML manually after every Build.

Does anyone know how I can solve this Issue? A correct automatic Mapping between External- and Output-Columns would be great, but at least the option to define the Mapping myself would be ok.

Any Help is appreciated!

Greetings Marco

Edit As requested a Minimal Example for better understanding:

  • The column in the ODBC Source (Postegres) has the type "text" (Columnname: description)
  • I select it in a ODBC-Source with this Query (DirectInput): SELECT description::varchar(4000) from mySourceTable
  • The ODBC-Source in Biml looks like this: <OdbcSource Name="mySource" Connection="mySourceConnection"> <DirectInput>SELECT description::varchar(4000) from mySourceTable</DirectInput></OdbcSource>
  • If I now generate the dtsx-Package the ODBC-Source throws the above mentioned warnings with the above mentioned Datatypes for External and Output-Column
Solution

  • As mentioned in the comment before I got an answer from another direction:

    You have to use DataflowOverrides in the ODBC-Source in BIML. For my example you have to do something like this:

    `<OdbcSource Name="mySource" Connection="mySourceConnection">
  <DirectInput>SELECT description::varchar(4000) from mySourceTable</DirectInput>
  <DataflowOverrides>
    <OutputPath OutputPathName="Output">
      <Columns>
        <Column ColumnName="description" SsisDataTypeOverride="DT_WSTR" DataType="String" Length="4000" />
      </Columns>
    </OutputPath>
    <OutputPath OutputPathName="Error">
      <Columns>
        <Column ColumnName="description" SsisDataTypeOverride="DT_WSTR" DataType="String" Length="4000" />
      </Columns>
    </OutputPath>
  </DataflowOverrides>
</OdbcSource>`

    You won't have to do the Overrides for all columns, only for the ones you have mapping-Issues with.

    Hope this solution can help anyone who passes by.

    Cheers