SQL Server Agent Job stops SSIS Step with "unexpected error" and without any error informations

3

I am dealing with my problem on some Windows Server 2019 (Core) with one running SQL Server 2019 CU4 instance each.

What we try to do

We are currently building a data warehouse with distributed databases. The individual layers of the DWH are located on one database server each. The data exchange between the layers/servers takes place via SSIS ETLs, which use Linked Servers to reach the other layers and drag and drop data. Each layer also has its own SSIS service instance and executes the corresponding SSIS packets. The SSIS packages are called by SQL Server Agent jobs. We have a job that executes the SSIS packets (#1), which in turn calls another job (#2) as the last step, which after a short wait time executes the calling job (#1). Thus, controlled by schedules, a loop is created and data is continuously transferred with ETLs.

I hope this was not too much unnecessary background

The error

Basically the job is running and there are numerous successful executions. However, we are observing interruptions at job #1 without helpful information regarding the error. This means that the job history log refers to the SSIS log, which again only contains an "unexpected termination". In the SSIS log, we only see behavior that indicates that the ETL packet active at that time stopped after validation. Depending on the log level, nothing is logged at all, not even the execution of single packages of the project. The package where this error occurs is different and not limited to a specific one.

What I have already tried

  • Re-create the jobs and SSIS Enviroments by hand (scripted before)
  • Using the 32Bit Runtime
  • Upgrade the SSIS project/package version to 2019
  • Increase the log level to "verbose"
  • Patching the SQL Server to CU4
  • Save ssis dump files (couldn't find them or they weren't created)
  • Search Windows and SQL Server Logfiles

Does anyone have some suggestions or some ideas how to become more error specific informations?

Thank you very much and take care :)

UPDATE We have an error message (OLE DB 0xC0202009 and 0X80004005)! 0xC0202009 and 0X80004005 error in SSIS Report In order to exclude the use of environments as a cause, I manually set the parameters in the SSIS job step instead of overwriting them by selecting an environment.

Long story short: Today it turns out that the parameter for an OLE DB Connection String is not passed correctly.

The following is specified as a parameter in the job step: parameter specified in ssis job step

However, the following connection string is specified in the context of the error message: context of ssis error message Please note that some arguments are added twice to the parameter (red).

What could have caused that?

sql-server
ssis
linked-server
sql-server-agent
sql-server-2019
asked on Stack Overflow May 12, 2020 by user_AL • edited May 14, 2020 by user_AL

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0