Hi,
i had developed a package which integrate data from service now source to SQL database.
Steps below which i used for integration.
1. Downloaded Service now ODBC driver 1.0.9 and installed.
2.Created DSN for Service now source and Used DatasourceName as Link_Server.
3.Created linked server using the DatasourceName.
4.Write Open Query select statement to fetch the records from source.
but i am getting intermittent error when run package, Sometimes it runs successful and Sometimes it fails but in retry attempt it goes through.
What could be the problem.
Below is the error message for reference.
[OLE DB Source [45]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14
Description: "Cannot execute the query "SELECT
ASMT_ASSESSMENT_INSTANCE.taken_on AS [SENT_DATE]
,
ASMT_ASSESSMENT_INSTANCE.dv_state AS [STATE]
,
ASMT_ASSESSMENT_INSTANCE.sys_created_by AS [SURVEY_CREATED]
,
INC.number AS [TICKET_NUMBER]
,
ASMT_ASSESSMENT_INSTANCE.[trigger_table] AS TICKET_TYPE
,
ASMT_ASSESSMENT_INSTANCE.[user] AS SURVEY_USER
,
ASMT_ASSESSMENT_INSTANCE.dv_metric_type AS SURVEY_TYPE
,
cast(INC.dv_location as varchar(4000)) AS LOCATION
,
cast(INC.dv_location as varchar(4000)) AS N_Owner_MSC
,'Test Company' AS COMPANY
,
'SERVICE DESK' AS SURVEYCLASSIFICATION
,ASMT_ASSESSMENT_INSTANCE.due_date as [SCRUBDATE]
,
ASMT_ASSESSMENT_INSTANCE.sys_created_on as [SURVEYSTARTED]
,
ASMT_ASSESSMENT_INSTANCE.sys_created_on as [SURVEYINVITATIONDATE]
,
ASMT_ASSESSMENT_INSTANCE.NUMBER AS INSTANCEID
FROM asmt_assessment_instance ASMT_ASSESSMENT_INSTANCE
JOIN incident INC ON INC.sys_id=ASMT_ASSESSMENT_INSTANCE.trigger_id
WHERE INC.number IS NOT NULL
AND ASMT_ASSESSMENT_INSTANCE.sys_updated_on>='2018-10-22 14:47:31'" against OLE DB provider "MSDASQL" for linked server "Link_Server". ".
What I have tried:
Tried to change all the properties in management console like Java Heap size, Reduced batch size, increased time out and retry attempt.