Dear all,
I am rather new to BIML and with the help of this forum and documentation I was able to create a dataflow task to get data from an Oracle data source.
But now I'm having troubles with some data types. When the packages are created and I want to run them the VS_NEEDNEWMETADATA error pops up when the source has a CLOB or LONG data type.
The generated BIML looks like:
Dataflow Name="Copy data from source to destination">
Expressions>
Expression ExternalProperty="[Retrieve data from Siebel].[SqlCommand]">@[User::OracleQuery]/Expression>
/Expressions>
Transformations>
CustomComponent Name="Retrieve data from Siebel" LocaleId="None" ComponentTypeName="CB67CD40-126C-4280-912D-2A625DFAFB66" Version="1">
DataflowOverrides>
OutputPath ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" OutputPathName="Output">
Columns>
Column ColumnName="OBJECT_WHERE_CLAUSE" />
/Columns>
/OutputPath>
/DataflowOverrides>
CustomProperties>
CustomProperty Name="BatchSize" DataType="System.Int32" SupportsExpression="true" Description="The number of rows fetched in a batch.">1000/CustomProperty>
CustomProperty Name="PrefetchCount" DataType="System.Int32" SupportsExpression="true" Description="Number of pre-fetched rows.">0/CustomProperty>
CustomProperty Name="LobChunkSize" DataType="System.Int32" SupportsExpression="true" Description="Determines the chunk size allocation for LOB columns">32768/CustomProperty>
CustomProperty Name="DefaultCodePage" DataType="System.Int32" SupportsExpression="true" Description="The code page to use when code page information is unavailable from the data source.">1252/CustomProperty>
CustomProperty Name="AccessMode" DataType="System.Int32" TypeConverter="AccessMode" Description="The mode used to access the database.">1/CustomProperty>
CustomProperty Name="TableName" DataType="System.String" SupportsExpression="true" Description="The name of the table to be fetched.">/CustomProperty>
CustomProperty Name="SqlCommand" DataType="System.String" SupportsExpression="true" Description="The SQL command to be executed.">SELECT * FROM SIEBEL.CX_ATS_ADMIN WHERE LAST_UPD >= '2016-01-01'/CustomProperty>
/CustomProperties>
OutputPaths>
OutputPath Name="Error" IsErrorOutput="true">
OutputColumns>
OutputColumn Name="ROW_ID" Length="15" DataType="String" />
OutputColumn Name="CREATED" DataType="DateTime" />
OutputColumn Name="CREATED_BY" Length="15" DataType="String" />
OutputColumn Name="LAST_UPD" DataType="DateTime" />
OutputColumn Name="LAST_UPD_BY" Length="15" DataType="String" />
OutputColumn Name="MODIFICATION_NUM" Precision="10" DataType="Decimal" />
OutputColumn Name="CONFLICT_ID" Length="15" DataType="String" />
OutputColumn Name="NAME" Length="500" DataType="String" />
OutputColumn Name="ACTIVE" Length="1" DataType="String" />
OutputColumn Name="EXCLUDE_FAMILY_ASSIGNMENT" Length="1" DataType="String" />
OutputColumn Name="DB_LAST_UPD" DataType="DateTime" />
OutputColumn Name="OBJECT_WHERE_CLAUSE" DataType="AnsiString" CodePage="1252" />
OutputColumn Name="AC_NAME" Length="500" DataType="String" />
OutputColumn Name="AO_NAME" Length="500" DataType="String" />
OutputColumn Name="BS_NAME" Length="500" DataType="String" />
OutputColumn Name="DB_LAST_UPD_SRC" Length="50" DataType="String" />
OutputColumn Name="RELATED_TEAMS" Length="500" DataType="String" />
OutputColumn Name="SOURCE_CONTAINER" Length="500" DataType="String" />
OutputColumn Name="BULK_ASSIGN_FLG" Length="1" DataType="String" />
OutputColumn Name="GROUP_ASSIGN_FLG" Length="1" DataType="String" />
/OutputColumns>
/OutputPath>
OutputPath Name="Output" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent">
OutputColumns>
OutputColumn Name="ROW_ID" Length="15" DataType="String" ExternalMetadataColumnName="ROW_ID" />
OutputColumn Name="CREATED" DataType="DateTime" ExternalMetadataColumnName="CREATED" />
OutputColumn Name="CREATED_BY" Length="15" DataType="String" ExternalMetadataColumnName="CREATED_BY" />
OutputColumn Name="LAST_UPD" DataType="DateTime" ExternalMetadataColumnName="LAST_UPD" />
OutputColumn Name="LAST_UPD_BY" Length="15" DataType="String" ExternalMetadataColumnName="LAST_UPD_BY" />
OutputColumn Name="MODIFICATION_NUM" Precision="10" DataType="Decimal" ExternalMetadataColumnName="MODIFICATION_NUM" />
OutputColumn Name="CONFLICT_ID" Length="15" DataType="String" ExternalMetadataColumnName="CONFLICT_ID" />
OutputColumn Name="NAME" Length="500" DataType="String" ExternalMetadataColumnName="NAME" />
OutputColumn Name="ACTIVE" Length="1" DataType="String" ExternalMetadataColumnName="ACTIVE" />
OutputColumn Name="EXCLUDE_FAMILY_ASSIGNMENT" Length="1" DataType="String" ExternalMetadataColumnName="EXCLUDE_FAMILY_ASSIGNMENT" />
OutputColumn Name="DB_LAST_UPD" DataType="DateTime" ExternalMetadataColumnName="DB_LAST_UPD" />
OutputColumn Name="OBJECT_WHERE_CLAUSE" DataType="AnsiString" CodePage="1252" ExternalMetadataColumnName="OBJECT_WHERE_CLAUSE" />
OutputColumn Name="AC_NAME" Length="500" DataType="String" ExternalMetadataColumnName="AC_NAME" />
OutputColumn Name="AO_NAME" Length="500" DataType="String" ExternalMetadataColumnName="AO_NAME" />
OutputColumn Name="BS_NAME" Length="500" DataType="String" ExternalMetadataColumnName="BS_NAME" />
OutputColumn Name="DB_LAST_UPD_SRC" Length="50" DataType="String" ExternalMetadataColumnName="DB_LAST_UPD_SRC" />
OutputColumn Name="RELATED_TEAMS" Length="500" DataType="String" ExternalMetadataColumnName="RELATED_TEAMS" />
OutputColumn Name="SOURCE_CONTAINER" Length="500" DataType="String" ExternalMetadataColumnName="SOURCE_CONTAINER" />
OutputColumn Name="BULK_ASSIGN_FLG" Length="1" DataType="String" ExternalMetadataColumnName="BULK_ASSIGN_FLG" />
OutputColumn Name="GROUP_ASSIGN_FLG" Length="1" DataType="String" ExternalMetadataColumnName="GROUP_ASSIGN_FLG" />
/OutputColumns>
ExternalColumns>
ExternalColumn Name="ROW_ID" Length="15" DataType="String" />
ExternalColumn Name="CREATED" DataType="DateTime" />
ExternalColumn Name="CREATED_BY" Length="15" DataType="String" />
ExternalColumn Name="LAST_UPD" DataType="DateTime" />
ExternalColumn Name="LAST_UPD_BY" Length="15" DataType="String" />
ExternalColumn Name="MODIFICATION_NUM" Precision="10" DataType="Decimal" />
ExternalColumn Name="CONFLICT_ID" Length="15" DataType="String" />
ExternalColumn Name="NAME" Length="500" DataType="String" />
ExternalColumn Name="ACTIVE" Length="1" DataType="String" />
ExternalColumn Name="EXCLUDE_FAMILY_ASSIGNMENT" Length="1" DataType="String" />
ExternalColumn Name="DB_LAST_UPD" DataType="DateTime" />
ExternalColumn Name="OBJECT_WHERE_CLAUSE" DataType="AnsiString" CodePage="1252" />
ExternalColumn Name="AC_NAME" Length="500" DataType="String" />
ExternalColumn Name="AO_NAME" Length="500" DataType="String" />
ExternalColumn Name="BS_NAME" Length="500" DataType="String" />
ExternalColumn Name="DB_LAST_UPD_SRC" Length="50" DataType="String" />
ExternalColumn Name="RELATED_TEAMS" Length="500" DataType="String" />
ExternalColumn Name="SOURCE_CONTAINER" Length="500" DataType="String" />
ExternalColumn Name="BULK_ASSIGN_FLG" Length="1" DataType="String" />
ExternalColumn Name="GROUP_ASSIGN_FLG" Length="1" DataType="String" />
/ExternalColumns>
/OutputPath>
/OutputPaths>
Connections>
Connection Name="ORACLEConnection" ConnectionName="ImportedProjectConnection2" />
/Connections>
/CustomComponent>
OleDbDestination Name="Load data into STG database" ConnectionName="ImportedProjectConnection1">
ExternalTableOutput Table="[SIEBEL].[CX_ATS_ADMIN]" />
/OleDbDestination>
/Transformations>
/Dataflow>
When looking at the code from the package it self (view code option in visuale studio) there is only 1 distinct difference:
Before metadata update:
externalMetadataColumn
refId="Package\Copy data from source to destination\Retrieve data from Siebel.Outputs[Output].ExternalColumns[OBJECT_WHERE_CLAUSE]"
codePage="1252"
dataType="str"
name="OBJECT_WHERE_CLAUSE" />
versus
externalMetadataColumn
refId="Package\Copy data from source to destination\Retrieve data from Siebel.Outputs[Output].ExternalColumns[OBJECT_WHERE_CLAUSE]"
codePage="1252"
dataType="text"
name="OBJECT_WHERE_CLAUSE" />
So only the data type has changed within the code part of the SSIS package, but the BIML script remains the same.
Can someone help how I can set the externalMetadataColumn directly to text instead of str?
Thanks you very much in advance!
