Simple SSIS package that is successful, but "fails"
-
We have an SSIS package that runs at midnight daily. It pulls data from Active Directory using ADSI query and pushes the data to a CRM.
The only time it "fails" is on Sunday night. Here is the rub: it is successful, and it sends an email that it succeeded, but we receive an error message that it failed from SQL Sentry, the Job Report in SSRS, and the SQL Job History. The query almost always completes in under two minutes, and we see the data in the CRM.
There are no other jobs running at the time of execution nor are there are jobs that started earlier and are still running at midnight. This started four weeks ago and nobody has made any changes. When we approached our infrastructure/systems team to inquire about any changes, they said no and suggested it could be from backups. There are no native SQL backup jobs running at the time. I doubt it is Veeam causing this problem, but I thought I'd mention it anyway.
This SQL Server version is 14.0.3465.1.
Windows Server 2016 Standard version 1607 and is fully patched as of this past Wednesday.
This is the error message and query:
select q1.employeeNumber, 'telephoneNumber'=CASE WHEN right(dbo.udf_GetNumeric(q1.telephoneNumber),10)='0' THEN null ELSE right(dbo.udf_GetNumeric(q1.telephoneNumber),10) END,
'mobile'=CASE WHEN right(dbo.udf_GetNumeric(q1.mobile),10)='0' THEN null ELSE right(dbo.udf_GetNumeric(q1.mobile),10) END,
q1.company, q1.Country, q2.[3DigitCode], q2.CountryCode
from (SELECT employeeNumber, telephoneNumber, mobile, company, 'Country'=CASE WHEN co is null THEN 'United States' ELSE co END
FROM OPENQUERY( ADSI, 'SELECT employeeNumber, telephoneNumber, mobile, mail, company, co, c, countryCode
FROM ''LDAP://MYSERVER/OU=MYCOMPANY USERS,DC=MYDOMAIN,DC=com''
WHERE objectCategory = ''Person''
and objectClass = ''User''
and employeeNumber>=0
and employeeNumber<200000
and userAccountControl<>''514''
and userAccountControl<>''66050''')) as q1 LEFT OUTER JOIN
Map_AD_Codes q2 on q2.Country = q1.Country
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else. -
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
After much digging and looking at performance, we found the cause to be high I/O. We used SQL Sentry, DMVs, and logs to determine that it might have been caused by third-party backups, in this case, Veeam.
We worked with our infrastructure/sysadmin group and determined those full backups, specifically on Sunday nights. We could see that full backups took longer over time, eventually affecting the SSIS jobs.
I appreciate anyone who took the time to read the question, even if no responses were given.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply