December 15, 2021 at 5:20 pm
Hello, I have a SQL Job that runs daily at 11pm. Recently we have noticed that pkgs being executed in a job step are being executed twice with a start time almost the same. The pkg All Execution history shows that each run was under a different service acct. I've used RedGate SQL Search and searched the pkg name being called but Im not finding anything else that would be calling it. Is there another way to find what is calling this pkg?
December 15, 2021 at 7:09 pm
Offhand, the best way I have found to search for it is to use SQL Search OR to script out all of the jobs and do a search in those. What makes it challenging though is that with SSIS, other servers can call the package.
What I mean is if SSIS is installed on server A and SQL instance B but on server C you have SQL instance D, D can call the SSIS package on B.
The place I would start looking is on the test instance of your SSIS box IF all of the steps in that job are running twice at nearly the same time (1 or 2 second differences). Chances are someone restored something to test and copied the jobs across pointing at the LIVE instance instead of test.
Alternately, if you have any failover or secondary SSIS instance in place, might not hurt to look at those instances to make sure they are not calling the SSIS package on your primary server like that.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 15, 2021 at 10:05 pm
Can you identify each service account - and where that service account is being used? If the service account is a proxy account - it will be defined in the credentials section under Security. If the service accounts are those that are defined to run the agent - then you need to look at each server where that service account was setup/configured to run the service.
In SQL Server agent - the agent job can be configured to connect to any valid SSIS catalog instance. It is most likely that an agent job on another server is configured to use the same SSIS catalog and that is running the second instance of the package.
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
December 16, 2021 at 1:06 pm
Do you use any other software (outside of SQL Agent) to run scheduled tasks?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply