Azure Data Factory with Self Hosted Integration Runtime Error

Question

Hi, I'm trying to get a self hosted runtime working that will execute a package with ServiceNow ODBC driver inside a SSIS package run through a ADF pipline via SSIS activity. I am getting the errors below, not sure what is wrong or where else I should be looking. It has worked a few times, but rarely. I am getting the same results when using either an Azure VM and on premise server to run my self hosted runtime. I have created a ADF ServiceNow ODBC Linked Service pointed to my self hosted runtime and executed it in a ADF pipeline and seems to be working fine. Some error logs I'm getting are below.

The package I'm attempting to execute is just a simple data flow task with ServiceNow as source and Azure SQL DB as destination.

SSISTelemetry Logs

EventMessageId,OperationId,MessageTime,MessageType,MessageSourceType,MessageText,ExtendedInfoId,PackageName,EventName,MessageSource,MessageSrcId,SubComponentName,PackagePath,ExecutionPath,ThreadId,PackageLocationType,PackagePathFull,MessageCode
d979bacf-f51b-4197-b527-fa79394c32d5,1,2020-09-03 01:28:52 -04:00,70,20,No web proxy set. Use system default proxy.,,,OnInformation,,,,,,0,,,
ffefba50-5a4b-4aa9-bd93-75427f8c3d30,1,2020-09-03 01:28:52 -04:00,10,30,{13AA9C8E-3B5A-495A-BF0F-FCFA063E38DE}:Validation has started.,,,OnPreValidate,{13AA9C8E-3B5A-495A-BF0F-FCFA063E38DE},{13AA9C8E-3B5A-495A-BF0F-FCFA063E38DE},,\Package,{13AA9C8E-3B5A-495A-BF0F-FCFA063E38DE},9304,,,
c2f65e54-a191-4bdb-86ba-8659f3fec86a,1,2020-09-03 01:28:52 -04:00,10,40,{348E4752-666E-4392-B403-1C5A29BF74C0}:Validation has started.,,,OnPreValidate,{348E4752-666E-4392-B403-1C5A29BF74C0},{348E4752-666E-4392-B403-1C5A29BF74C0},,\Package{348E4752-666E-4392-B403-1C5A29BF74C0},{13AA9C8E-3B5A-495A-BF0F-FCFA063E38DE}{348E4752-666E-4392-B403-1C5A29BF74C0},9304,,,
4a31bf62-ebb7-4573-8902-dcb43d39ba65,1,2020-09-03 01:28:52 -04:00,70,40,{348E4752-666E-4392-B403-1C5A29BF74C0}:Information: Validation phase is beginning.,,,OnInformation,{348E4752-666E-4392-B403-1C5A29BF74C0},{348E4752-666E-4392-B403-1C5A29BF74C0},SSIS.Pipeline,\Package{348E4752-666E-4392-B403-1C5A29BF74C0},{13AA9C8E-3B5A-495A-BF0F-FCFA063E38DE}{348E4752-666E-4392-B403-1C5A29BF74C0},9304,,,1074016266
1e534391-2b2a-4819-bcda-359536efe9e8,1,2020-09-03 05:28:57 +00:00,120,70,SSIS Executor exit code: -1073741819.,,,Terminated,WorkerAgent,,,,,0,,,

[SSISExecutorOnSelfHostedIR][{null}][Information][vac21dbsicm800][34][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:28:50.7985910+00:00] OperationGuid=21d46da9-b653-429d-807d-30f2c70b0fe6.
[2020-09-03T05:28:50.7985910+00:00] OperationId=1.
[2020-09-03T05:28:50.7985910+00:00] LogUse32bitRuntime=False.
[2020-09-03T05:28:50.7995844+00:00] Start retrieving package to execute.
[2020-09-03T05:28:50.8005870+00:00] Credential set for package execution.
[2020-09-03T05:28:50.8005870+00:00] Package to execute is retrieved.
[2020-09-03T05:28:50.8025846+00:00] Named Pipe 'ISWorkerAgent\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' started.
[2020-09-03T05:28:50.8035927+00:00] Named Pipe 'ISWorkerAgentLog\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' started.
[2020-09-03T05:28:50.8055994+00:00] Named Pipe 'ISWorkerAgentTelemetry\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' started.
[2020-09-03T05:28:50.8075889+00:00] Named Pipe 'ISWorkerAgentRuntimeResource\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' started.
[2020-09-03T05:28:50.8075889+00:00] Start SSIS Executor.
[2020-09-03T05:28:50.8405851+00:00] Executor Process is started successfully with pId=4584.
[2020-09-03T05:28:50.8405851+00:00] SSIS Executor started.
[2020-09-03T05:28:57.3335401+00:00] SSIS Executor exited.
[SSISExecutorOnSelfHostedIR][{null}][Error][vac21dbsicm800][34][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:28:57.3335401+00:00] Package Executor doesn't report a completed status before exit.
[SSISExecutorOnSelfHostedIR][{null}][Information][vac21dbsicm800][34][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:28:57.3335401+00:00] SSIS Executor exited.
[SSISExecutorOnSelfHostedIR][{null}][Information][vac21dbsicm800][32][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:29:06.5410944+00:00] Retry application event logging.
[SSISExecutorOnSelfHostedIR][{null}][Information][vac21dbsicm800][34][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:29:08.3236453+00:00] Named Pipe 'ISWorkerAgentTelemetry\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' stopped.
[2020-09-03T05:29:08.8251271+00:00] Named Pipe 'ISWorkerAgentRuntimeResource\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' stopped.
[2020-09-03T05:29:09.4257448+00:00] Named Pipe 'ISWorkerAgentLog\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' stopped.
[2020-09-03T05:29:10.0130420+00:00] Named Pipe 'ISWorkerAgent\d4ea2cd1-87c0-4ce4-bb29-1e68049a9055' stopped.
[2020-09-03T05:29:10.0150421+00:00] Task finished with Status: Terminated, More Detail: None

Event Viewer

Log Name: Connectors – Integration Runtime
Source: Integration Runtime (Self-hosted) Connectors
Date: 9/3/2020 1:28:57 AM
Event ID: 30000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: vac21dbsicm800
Description:
[SSISExecutorOnSelfHostedIR][{null}][Error][vac21dbsicm800][34][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:28:57.3335401+00:00] Package Executor doesn't report a completed status before exit.

Log Name: Connectors – Integration Runtime
Source: Integration Runtime (Self-hosted) Connectors
Date: 9/3/2020 1:28:57 AM
Event ID: 30000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: vac21dbsicm800
Description:
DEBUG:
TraceComponentId: ExecutionTask
TraceMessageId: ExecutionSsisActivityExecutorTrigger
@logId: Error
jobId: 00000000-0000-0000-0000-000000000000
activityId: 00000000-0000-0000-0000-000000000000
eventId: ExecutionSsisActivityExecutorTrigger
message: [SSISExecutorOnSelfHostedIR][{null}][Error][vac21dbsicm800][34][{null}][TaskTrackerLogTag#21d46da9-b653-429d-807d-30f2c70b0fe6#d4ea2cd1-87c0-4ce4-bb29-1e68049a9055#21d46da9-b653-429d-807d-30f2c70b0fe6]
[2020-09-03T05:28:57.3335401+00:00] Package Executor doesn't report a completed status before exit.

Log Name: Connectors – Integration Runtime
Source: Integration Runtime (Self-hosted) Connectors
Date: 9/3/2020 1:29:14 AM
Event ID: 30000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: vac21dbsicm800
Description:
Execution failed. Error message: 'Type=Microsoft.DataTransfer.Execution.Core.ExecutionException,Message=Package execution failed. For more details, select the output of your activity run on the same row.,Source=Microsoft.DataTransfer.Executions.RuntimeTask,StackTrace= at Microsoft.DataTransfer.Execution.RuntimeTask.ActivityExecutorWorkerAdapter.<ExecuteAsync>d__1.MoveNext(),'
Job ID: 21d46da9-b653-429d-807d-30f2c70b0fe6
Log ID: Error

Log Name: Connectors – Integration Runtime
Source: Integration Runtime (Self-hosted) Connectors
Date: 9/3/2020 1:29:14 AM
Event ID: 30000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: vac21dbsicm800
Description:
DEBUG:
TraceComponentId: ExecutionTask
TraceMessageId: ExecutionTaskCommon
@logId: Error
FunctionName: ExecutionTask
jobId: 21d46da9-b653-429d-807d-30f2c70b0fe6
activityId: 21d46da9-b653-429d-807d-30f2c70b0fe6
eventId: ExecutionTaskCommon
message: Execution failed. Error message: 'Type=Microsoft.DataTransfer.Execution.Core.ExecutionException,Message=Package execution failed. For more details, select the output of your activity run on the same row.,Source=Microsoft.DataTransfer.Executions.RuntimeTask,StackTrace= at Microsoft.DataTransfer.Execution.RuntimeTask.ActivityExecutorWorkerAdapter.<ExecuteAsync>d__1.MoveNext(),'

Errors from Package OnError Event

ConnectByProxy: Process data from proxy to pipeline buffer fail since error: Timeout when reading from staging. TaskStatus: Failed, ErrorCode: 2906, ErrorMessage: Package execution failed. For more details, select the output of your activity run on the same row., Output: {
"OperationErrorMessages": "9/3/2020 5:11:18 AM +00:00 : SSIS Executor exit code: -1073741819.\n",
"LogLocation": "C:\ProgramData\SSISTelemetry\ExecutionLog\2d00f1b2679747b5a1ec36edb44fd622",
"effectiveIntegrationRuntime": "ICM-GOV-INT-SOUTH-DF1-IRSH1",
"executionDuration": 33,
"durationInQueue": {
"integrationRuntimeQueue": 1
},
"billingReference": {
"activityType": "DataMovement",
"billableDuration": [
{
"meterType": "SelfhostedIR",
"duration": 0.016666666666666666,
"unit": "Hours"
}
]
}
}

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ServiceNow ODBC Source returned error code 0xC020F465. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Answer 1

The only reason I was running in self-hosted IR was because the ServiceNow driver installer doesn't run via command line. Anyway, another guy I work with hacked the installer up and we can now install the driver via main.cmd, and it looks like it's working good in SSIS-IR. Thanks.