Custom SSIS Integrated Runtime Package execution difference

Question

Custom SSIS Integrated Runtime Package execution difference

DENNISON, John 31

Hi,

I have a number of packages that are displaying different behaviour when executed within the SSIS-IR environment. That packages run correctly on the development environment using the same source & targets, however when I deployed to the IR and execute I get strange errors that may be related to character encodings or something similar. For example :Invalid character value for cast specification

What are the regional settings used for the SSIS-IR? Could this explain the differences? How can I configure / develop my packages in order to eliminate this type of problem?

Thanks,
John

HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2021-05-10T18:51:57.76+00:00

Hello @DENNISON, John ,
Thanks for the ask and using the Microsoft Q&A platform .
How did you deployed the package on SSISDB for filesysytem ? I think if you provide more details of the error that could help . Also please eloborate more on the data itself . The error is on the cast failure which can happen for many reasons .

Will wait to hear from you this .

Please do let me know how it goes .
Thanks
Himanshu
HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2021-05-12T18:26:44.007+00:00

Hello @DENNISON, John ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2021-05-18T22:16:04.12+00:00

Hello @DENNISON, John ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
DENNISON, John 31 Reputation points

2021-05-19T05:36:24.697+00:00

I have previously tried to reply however the comments where never posted. I am trying this last time.

In the end I discovered what was causing the issue. It appeared that the order of the columns in the ODBC source were being used instead of the name of the column. Have no idea how this could explain the difference between execution in Visual Studio and via the SSIS-IR. However analyzing the package I noticed that the name of the column that was overflowing was displayed last in the source column list even though it was fourth in the sql query. Configuring an file destination on conversion error, I noticed that the data being used was in fact the last column of the query.

From there I removed and recreated the ODBC sources and everything worked fine. The project had been updated from 2012 to 2017 so this may have been the cause.

Regards
John
DENNISON, John 31 Reputation points

2021-05-19T05:36:53.12+00:00

The packages are deployed to a SSISDB on a S0 SQL Instance.

When I execute the packages on a development machine, which is in the same vnet as the adf ssis-ir everything works fine, however when I exeucte from the SSIS-IR i get some strange errors. The packages are configured to use exactly the same source & target databases, and exactly the same drivers are installed on the SSIS-IR as the dev machine. The source is a MySQL Database, using the 8.0.15 unicode driver, the target a SQL Elastic Pool database.

The first time I ran the package I got a truncation error on the source, which I didn't get on the development machine, so I explicitly cast the values to the length defined on the output columns. This corrected this initial error, however now I am getting other messages that seem to be linked to the way the SSIS-IR is handling the strings.

The message doesn't state the column in error, hence I am struggling to identify the source,
Any help appreciated.
Thanks,
John
DENNISON, John 31 Reputation points

2021-05-19T05:38:25.68+00:00

It appears that my previous comment were too long and we not being published, even though below the submission form it said that I had characters left to use.