Input string was not in a correct format

Bobby P 221 Reputation points
2022-05-03T19:01:05.217+00:00

So we have a data column [WAC]...Wholesale Acquisition Cost. The source data column is defined as [price] NUMERIC(12,6). We are looking to update our SQL Server Table and data column [WAC] which is defined exactly the same...NUMERIC(12, 6)

When we source the data from our source table, [price], we are storing it into a Package Variable called WAC...We have tried defining both the Package Variable AND the Execute SQL Task Parameter Mapping as...

  • DECIMAL
  • DOUBLE

It makes no sense that I would have to CONVERT the extract of the source column to DECIMAL(12,6)...Or does it?

Why is the SSIS Package Data Type interpretation have to be sooooo difficult and intuitive?

Thanks for your review and am hopeful for an answer.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,508 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,726 Reputation points
    2022-05-03T19:08:55.227+00:00

    Here is a good link for you, outlining all three data types is SSIS:

    • SSIS Data Type
    • SSIS Expression
    • SQL Server

    SSIS:Data Type Conversions

    0 comments No comments

  2. ZoeHui-MSFT 34,996 Reputation points
    2022-05-04T07:38:55.303+00:00

    Hi @Bobby P ,

    Set the datatype of the variables as string and then pass it to the parameter with NUMERIC.

    In addition, why not use sql command in the Execute SQL Task such as

    insert into destination table(price) select wac from source table  
    

    May I know how do you set the value of the variables?

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments