SSIS - Data Conversion Truncation error

  • SQL Server

    Old Hand

    Points: 346

    Hi Folks:

    Need help with SSIS package. I have created package using the CSV Input file (file attached). In the Input file I have the following records in the end which we don't want to load to SQL table. In the package I am using conditional split to filter those records and it works for some records, but does not work for the ones highlighted in bold below: It throws the error "Data conversion failed. The data conversion for column "Legal Entity" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    Rows Returned: 9

    Close of Business Balances for Region: NAM

    Base Currency: USD Subtotal By: Name Show Detail: Yes Use A/C Base Curr: No CCP/Product: Yes Show IM/VM Excess/Deficit: No Base Curr Reporting: No

    "USD Equivalent: 23,345.20"

    "USD Equivalent Excess: 14,059.48"

    "USD Equivalent Deficit: 4,456.80"

    "WM/Reuters Closing Rates as of 4:00 pm London time (Jan 09 2020)"

    "Close of Business: Jan 10, 2020"

    "ABC Online"

    "https://www.abccity.com"

     

    CONDITIONAL SPLIT:

    LEFT(TRIM([Legal Entity]),4) == "Rows" || TRIM([Legal Entity]) == ""

    LEFT(TRIM([Legal Entity]),5) == "Close" || TRIM([Legal Entity]) == ""

    LEFT(TRIM([Legal Entity]),5) == "USD E" || TRIM([Legal Entity]) == ""

    LEFT(TRIM([Legal Entity]),4) == "Citi" || TRIM([Legal Entity]) == ""

    LEFT(TRIM([Legal Entity]),5) == "https" || TRIM([Legal Entity]) == ""

    LEFT(TRIM([Legal Entity]),10) == "WM/Reuters" || TRIM([Legal Entity]) == ""

    LEFT(TRIM([Legal Entity]),4) == "Base" || TRIM([Legal Entity]) == ""

     

    • This topic was modified 6 days, 8 hours ago by  SQL Server.
    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 243915

    SSIS assumes a default length of 50 for CSV columns. You need to increase that ... do you need help in understanding how to do that?

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply