SSIS Package is writing corrupt data, but only when run as a Job
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.
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.
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.