Post reply

Upgrade to SQL2022 makes packages that connect to Oracle fail with data errors

  • June 23, 2023 at 4:25 pm

    #4217741

    We have 3 environments, dev, uat and prod. We've started to test SQL2022 by doing an in-place upgrade to SQL2022 across all of the dev server. So SQL, SSIS, SSRS and SSAS are now SQL2022. Everything was SQL2017 prior to this.

    It seems the only fallout is that the SQL Agent jobs on our dev server that launch SSIS packages that use an Oracle database server as a data source in their dataflows fail. Well, some do. Some don't. It seems to be the same 3 errors though...

    • System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime. at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day) at System.DateTime..ctor(Int32 year, Int32 month, Int32 day, Int32 hour, Int32 minute, Int32 second, Int32 millisecond) ....

      • these packages are the ones that use a where clause on the select to pull data from oracle only from a certain datetime.

    • Data conversion failed while converting column "SourceOracleColumnName" (292) to column "DestinationOracleColumnName" (48). The conversion returned status value 6 and status text "Conversion failed because the data value overflowed the specified type.".
    • Data conversion failed while converting column "SourceOracleColumnName" (267) to column "DestinationOracleColumnName" (75). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

     

    I don't understand why these fail....other packages of the same nature (i.e connect to Oracle to pull data), also use where clauses to pull data from Oracle using a particular datetime, or have the same sort of data types in them to don't report conversion or truncation errors.

     

    Also our packages are actually built using a TeamCity server running COnfiguration Projects that run powershell scripts that call MSBUILD.EXE and in conjunction with BIMLStudio build our SSIS projects from a database full of metadata describing the data sources/flow/destinations (Along with data types and everything).

    Incidentally, UAT and Prod, untouched sitting at SQL2017, that connect to the very same Oracle data source continue to work.

    I haven't upgraded any packages to SQL 2022. They're being built as a SQL2017 project and deployed as such, but to a SQL2022 SSISDB.

    The SQL2017 (14.0) SSIS Service is stopped and disabled. The SQL2022 SSIS service (16.0) is automatic and running.

     

    The SQL Agent job running the packages reports as being "Executed as user: mydomain\mysvcacc. Microsoft (R) SQL Server Execute Package Utility Version 16.0.1000.6 for 64-bit Copyright (C) 2022 Microsoft. "

     

    All help appreciated!

    • This topic was modified 1 year, 5 months ago by  stiej1977.
    • This topic was modified 1 year, 5 months ago by  stiej1977.

  • June 23, 2023 at 4:37 pm

    #4217745

    Those data conversion errors should be investigated in VS. Are they completely bogus, or maybe there is an easy fix?

    The other error I have never seen before.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 23, 2023 at 4:43 pm

    #4217747

    thanks. i have tried debuggin in VS on my local machine, but i've got a fundamental issue of trying to work out which oracle drivers i need in order for the data source to even connect to that oracle database. I've installed oracle's own drivers (not an easy or clear process) and i've tried the latest OLEDB MS Oracle driver (v1.2?) and also the latest version (v5) of the Attunity MS drivers. it looks like the package uses the Oracle driver (i.e Oracle themselves, not MS Oracle) as it is set up to take tnsnames entry with user name and password, and that's it. the tnanames.ora file contains the rest of the oracle server name connection string, but like i say, i can't get it to connect anyway in order to try debuggin in VS on my machine. Trying to work that out, until then i'ma bit blind. i do have VS on the SQL Server itself, but that wont fire up until i get our IT to enable/allow cookies, as i can't otherwise sign into VS. So I'm stuck there too for the moment.

  • June 23, 2023 at 5:58 pm

    This was removed by the editor as SPAM

  • June 23, 2023 at 6:00 pm

    This was removed by the editor as SPAM

  • June 24, 2023 at 12:40 pm

    #4218342

    I must be missing something, but can't you just replicate what's on the server, in terms of drivers?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 total)

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