Intermittent SSIS step failures of job that runs every 30 minutes
-
We recently upgraded to SQL Server 2016, VS2017/SSDT.
There is one job that runs every 30 minutes throughout the day which succeeds 90% of the time, but occasionally fails with the following error:
My initial thought was that the sftp transfer was grabbing the file at the same time this job is trying to write to it, but the transfer job runs every 10 minutes on the 8s (HH:08, HH:18 etc) and take less than a minute to run. The SQL Server Agent job runs at HH:05 and HH:35 and also takes less than a minute to complete. A proxy is being used. The job always ran successfully prior to the upgrade.
If this were a credential/permissions issue I would expect it to always fail.
Any suggestions?
Thanks in advance.
Some new info...it seems to be creating the flat file anyway: -
How are the SSIS packages deployed? Hopefully you transitioned to project deployment - and deployed to the integration services catalog. If not - then you need to consider doing that as it will definitely help in these types of situations. The catalog has built in reports that allow you to dig into the messages from the package to identify what task failed and why.
Looking at your message - it appears that there is an issue opening the datafile. If I saw that type of message in my package - I would review the security on the destination to insure the service account or proxy account has appropriate permissions to that location. If that is confirmed then I would review to insure the file that exists has the correct permissions and is not locked by another application.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
This file is on a shared folder. I think this is not a problem of SSIS but some other thing. Probably you have changed Windows version, some different configuration, domain controller or even hardware problems. There are many potential causes for intermittent drops or disconnects from a shared folder. Search in that direction...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply