Developer forum

Hi Tobias,
that is because numeric sql type corresponds to .NET decimal type: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings.

And now there is no conversion implemented in Data integration from source types other than string to .NET int/long/double/etc.

You can try to use the TableScript functionality and write your custom conversion code from "numeric" to "double" so in the ProcessInputRow method you can
assign your row["NumericColumn"] = (double) your DecimalOrNumericValueConvertedToDouble so in that way there will be no need to make any conversions as your source and destination columns will have same type (double)
Regards, Dmitrij

Hi Dmitriy.

I have already tried to make a TableScript, where I tried to cast the value to either double or float but it didn't work. I still got the same error.

This is my code:

    public class ProductTableScript : TableScript
    {
        public override string ScriptingName
        {
            get
            {
                return "C5 InventPrice";
            }
        }

        public override void ProcessInputRow(Mapping mapping, Dictionary<string, object> row)
        {
            if (mapping.SourceTable != null)
            {
                var column = mapping.SourceTable.Columns.FirstOrDefault(c => c != null && c.Name.ToLower() == "Price".ToLower());
                if (column != null && row.ContainsKey(column.Name))
                {
                    var value = row[column.Name].ToString();
                    var culture = CultureInfo.GetCultureInfo("da-DK");
                    var test = float.Parse(value, culture);
                    row[column.Name] = test.ToString("#.##", CultureInfo.CreateSpecificCulture("en-US"));
                }

                column = mapping.SourceTable.Columns.FirstOrDefault(c => c != null && c.Name.ToLower() == "PRICEUNIT".ToLower());
                if (column != null && row.ContainsKey(column.Name))
                {
                    var value = row[column.Name].ToString();
                    row[column.Name] = float.Parse(value).ToString();
                }
            }
        }
    }

Best regards, Tobias

Hi Tobias,
try:

row[column.Name] = double.Parse(some string value);
instead of storing the string value.
Regards, Dmitrij

Hi Dmitriy.

I tried doing:

row[column.Name] = double.Parse(row[column.Name].ToString());

I got an error message similar to the other one: Job Failed with the following message: cannot parse '40' to a double. Table: EcomPrices. Column: PriceAmount

Best Regards,
Tobias

Could you add this in the begginning of your code to change the type of your source decimal column to double:
var priceColumn = mapping.GetColumnMappings().Where(cm => cm.SourceColumn != null && cm.SourceColumn.Name == "Price").FirstOrDefault();

if(priceColumn != null && priceColumn.Type != typeof(System.Double)){
priceColumn = new Column(priceColumn.Name, typeof(System.Double), priceColumn.Table, priceColumn.IsPrimaryKey, priceColumn.IsNew);
}
And do the same for another "PRICEUNIT" column
Regards, Dmitrij

I tried to change the code for price and priceunit to:

var columnMapping = mapping.GetColumnMappings().Where(cm => cm.SourceColumn != null && cm.SourceColumn.Name == "PRICE").FirstOrDefault();
                if (columnMapping != null && columnMapping.SourceColumn.Type != typeof(System.Double))
                {
                    var priceColumn = new Column(columnMapping.SourceColumn.Name, typeof(System.Double), columnMapping.SourceColumn.Table, columnMapping.SourceColumn.IsPrimaryKey, columnMapping.SourceColumn.IsNew);
                    row[priceColumn.Name] = double.Parse(row[priceColumn.Name].ToString());
                }

                columnMapping = mapping.GetColumnMappings().Where(cm => cm.SourceColumn != null && cm.SourceColumn.Name == "PRICEUNIT").FirstOrDefault();
                if (columnMapping != null && columnMapping.SourceColumn.Type != typeof(System.Double))
                {
                    var priceUnitColumn = new Column(columnMapping.SourceColumn.Name, typeof(System.Double), columnMapping.SourceColumn.Table, columnMapping.SourceColumn.IsPrimaryKey, columnMapping.SourceColumn.IsNew);
                    row[priceUnitColumn.Name] = double.Parse(row[priceUnitColumn.Name].ToString());
                }

I got the following error: Job Failed with the following message: cannot parse '40' to a double. Table: EcomPrices. Column: PriceAmount

Best Regards,
Tobias

Thank you for the help.

It seems to work ☺