Simple SSIS package that is successful, but "fails"

Question

Post reply

Simple SSIS package that is successful, but "fails"

Sean Perkins

Ten Centuries

Points: 1360
More actions
June 26, 2024 at 6:42 pm

#4431683
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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.