0

Our environment is the following:

SQL Server 2019 (15.0.4153.1) + SQL Integration Services (15.0.2000)

We have a DTSX Package that uses a OLE connection manager to connect to an SQL Server to read data from and an ODBC 32 Bit connection manager using the Oracle Instant Client 21 to write the data to.

The package itself is absolutely stupid and simple and just reads data from a view and without any modification writes the data to the target table.

We have to variants of the DTSX Package: the first (and original package which ran on an SQL Server 2012 for years without problems) has a transformation step included which casts the data read from the SQL Server to different types before writing them to the target table. E.g. DT_I4 was cast to DT_NUMERIC. It seems that this step was necessary back then, but can be omitted now.

Now the observation is that the package works perfectly fine when executed directly from Visual Studio (2019). The data is written correctly, here is a simplified example:

Given Name | Last Name
-----------------------
John       |  Doe
William    |  Smith
Claire     |  Anderson
Jane       |  Scott

But when run as a Job via the SQL Server Agent (details below), the result in the target table looks like this:

Given Name | Last Name
-----------------------
William    |  Smit
Willi      |  Smi
Wil        |  Smith
William    |  Sm

I have absolutely no clue how that can be even possible, because in my naive world (which usually does not involve Microsoft products, so I am absolutely not familiar with how SSIS actually works) rows are processed as rows and nothing like this can ever happen.

Not only that the rows are mixed up, the contents of the row are also corrupted and truncated arbitrarily.

Has anyone observed this problem and knows a solution?

The job we created consists of one single step which executes an Integration Service Package. We already tried a) reading the file directly from the file system and b) reading the file from a file storage on the SQL Server where we imported the DTSX. Both yield the same result. Integration Service Catalog is unfortunately not an option for us.

The package runs in the 32 bit environment due to the 32 bit ODBC driver we needed to use (because the Visual Studio 2019 where the package is built is 32 bit software).

The target server version configuration property in the package is set to SQL Server 2019 (was SQL Server 2012 before).

The most irritating things here are a) that the same DTSX Package ran for several years in an SQL Server 2012 without any problems and b) that the Package also runs from Visual Studio without any problems. Only when executed as a Job in the SQL Server, the data is completely corrupt.

More Information:

The data source for the Oracle Database is a system data source which is used by both the SQL Server and Visual Studio when executing. So I can rule out that different ODBC drivers are used.

Credentials for the Database are stored within the package and protection mode is EncryptSensitiveInformationWithPassword. The question in the comment was which permissions the user have on the Oracle Database. The user we use is the schema owner.

The user executing the package, though, is different, since it is the logged in user (local admin) on the machine when executing the package from Visual Studio and on the other hand, the package is executed by the SQL Server Agent and I suspect the SYSTEM User is doing that.

Improve this question
3
  • Glad it's working but if you continue running into issues, I'm rather confused on your problem description. You have an SSIS package that Reads data from SQL Server and writes to an Oracle table? When you run the package in Visual Studio, what is the user running the package and what permissions do they have in Oracle. When the data corrupting job executes, what user is it using to connect to Oracle? Are both packages using the same point release of the Oracle drivers? Do they both connect to the same server and database?
    – billinkc
    Jun 1, 2022 at 15:56
  • You indicate "tried reading the file directly from the file system" What file is this? Where does file storage come into play? Pictures are worth more than a thousand words in SSIS problem statements as there's a host of ways to solve a problem and the hoofbeats I hear may sound like horses but your perception is zebras. Table definitions of source and destination and since you reference a job, provide the job definition. It's the only way we get to a reproducible state.
    – billinkc
    Jun 1, 2022 at 16:01
  • @billinkc I added some more information below my post. "File System" / "File Storage" refers to the package source where file system means the file is specified by absolute pathname and file storage means the file is specified by selecting it from the "Stored Packages" within the SQL Server. I tried reading the changelog of the cummulative updates 13 to 16 but did not found any mention of this bug. I fear only someone who experienced and debugged this problem themselves can explain what went wrong. The rest of us must live with the fact that a recent update fixes the problem.
    – UniversE
    Jun 1, 2022 at 18:55

1 Answer 1

Reset to default
0

After patching the SQL Server to 15.0.4223 it started to work 99%.

There is a string column that is inserted into a number column in the target database. While that worked before, this still produced corrupt data, but only when run as a job.

However, that was easily fixed by converting the field to the correct type in the SSIS package.

So the solution is, so it seems, that there was a bug introduced in the SQL Server somewhere between 2012 and 2019 that has been fixed in more recent version.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.