Posted Tue, 22 Mar 2022 13:34:08 GMT by Pim Vrolijks

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!
Posted Wed, 13 Sep 2023 12:56:18 GMT by Jaxon Eli

Nice post! This is a very nice blog that I will definitively come back to more times this year! I just found this blog and have high hopes for it to continue. Keep up the great work, its hard to find good ones. I have added to my favorites. Thank You.I can set up my new idea from this post. It gives in depth information. Thanks for this valuable information for all,..This is just the information I am finding everywhere. This type of message always inspiring and I prefer to read quality content, so happy to find good place to many here in the post, the writing is just great, thanks for the post.   bitcoin code estafa

You must be signed in to post in this forum.