4

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?

user_AL
  • 66
  • 7
  • Why the 32 bit run time? – billinkc May 12 '20 at 16:36
  • In the past we had problems with the 64Bit environment, let's just call it "feeling" or "experience" that made me try this. The pilot test, in which everything worked so far, ran however with 64 bits. – user_AL May 12 '20 at 16:43
  • I've never seen that happen before so I can only assume it some kind of error in your package build. Does it do it every time? BTW you have an _very_ complicated setup there. Why do you need linked servers when you are using SSIS? It sounds like there is some designing going on here. – Nick.Mc May 14 '20 at 12:36
  • The error occurs randomly and some times in sequence. Design might be a thing. The main idea was to enable the different layers to scale out there specific resource requirements. Linked Server were used because that way we are able to dynamically script SQL Statements depending on (external) meta data of the tables, so we can use Package Parts more often. SSIS Dataflow sources and destinations can't be build dynamically without 3rd party tools. – user_AL May 14 '20 at 13:53
  • If your data flow is through linked server then you don't need SSIS at all – Nick.Mc May 16 '20 at 09:35
  • 1
    I understand that this impression could arise. We use the linked servers in the source of the dataflows to save lookup and convert components within the dataflow. In our experience these are significantly slower than joins. There are also other reasons that require certain data from Linked Server sources already in the DF source. If you ask me it can also be a goal to save dataflows completely and use SSIS only for orchestration - wherever possible. – user_AL May 18 '20 at 09:37

0 Answers0