ssis - Why did Biml Studio 2024 get error in handling data type `longtext` in our MySQL 8.0.xx? - Stack Overflow

We simplified the production database definition to a minimized example, and the error scenario happens when we have the follow DDL:

CREATE TABLE `sales_order_item_test_longtext` (
  `item_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Item ID',
  `product_options` longtext COMMENT 'Product Options',
  PRIMARY KEY (`item_id`)
) ;

And, we have the following test.biml script file. Given target_schema and table_name, it verifies that Biml can successfully retrieve the meta data of the table from the connection of RootNode.DbConnections["Source"].

<#
    string target_schema = "source_schema";
    var includedSchemas = new List<string>{target_schema};
    
    string table_name = "sales_order_item_test_longtext";
    var includedTables = new List<string>{table_name};
        
    var sourceMetaConnection = RootNode.DbConnections["Source"];
    var sourceMetadata = sourceMetaConnection.GetDatabaseSchema(includedSchemas, includedTables,
        ImportOptions.None);        
    var sourceTable = sourceMetadata.TableNodes.ElementAt(0);
#>

<#= "<!-- sourceMetadata.SchemaNodes count: " + sourceMetadata.SchemaNodes.ToArray().Count().ToString() + " -->" #>
<#= "<!-- sourceMetadata.TableNodes count: " + sourceMetadata.TableNodes.ToArray().Count().ToString() + " -->" #>

<Biml xmlns=".xsd"></Biml>

It gets the following errors in the view of "Preview Expanded BimlScript":

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.ThrowHelper.ThrowArgumentOutOfRangeException in :line 0
at System.Collections.Generic.List`1.get_Item in :line 0
at BimlScriptCode in C:\my\work\biml-studio\biml-studio-eservices\biml-studio-eservices\addedBiml\BimlScripts\test.biml:line 11

Although, the error message did not directly indicate toward this direction, we found out the root cause by testing.

The error is related to the longtext data type on the testing DDL above. If we remove the column of longtext type, e.g., by commenting it out like the following, the error disappears, and the script gets the expected result.

CREATE TABLE `sales_order_item_test` (
  `item_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Item ID',
--  `product_options` longtext COMMENT 'Product Options',
  PRIMARY KEY (`item_id`)
) ;

As the expected testing result, the DDL without longtext leads to the following output:

<!-- sourceMetadata.SchemaNodes count: 1 -->
<!-- sourceMetadata.TableNodes count: 1 -->

<Biml xmlns=".xsd"></Biml>

Tentatively, I am also including ssis in the question's tags because the issue might be related to the data type translation between SQL Server and MySQL. If I missed anything, please let me know.

We are working on an ETL from MySQL v8.0.x to SQL Server using SSIS, and we are using Biml Studio 2024 to generate the SSIS packages.

We used BimlExpress 2019 on Visual Studio 2015 before, and did not encounter this issue. So, we also hope to see if there is any workaround and return to the same system behavior as BimlExpress 2019.

We highly appreciate any hints and suggestions.