Lock an SSIS package from multiple simultaneous executions

1
votes

I have an SSIS package(Package1.dtsx) that been deployed to SSISDB. currently I scheduled the package with some parameters in sql server agent.

how do I lock the package(Package1.dtsx) if someone try to attempt running it in another sql server agent job with different parameters.

2
sql-serverssissql-server-2012sql-server-data-toolssql-server-agent
If its running from a SQL Server agent, just disable the execute rights to all other users which you don't want to give rights to. This should be fairly simple if your problem is that simple. - rvphx
All the jobs will use the same etl proxy account. but some users will start different sql server agent job at various times as needed. will this take care of locking the package? - user5947731

2 Answers

0
votes

You can do this yourself by adding a flag and having your package check this flag before processing. Either quit out, loop until flag is clear or some other logic.

I personally have only ever had one agent per package and the agent handles the multiple execution scenarios.

0
votes

Locking a package to prevent it from multiple executions is not possible. Think of it as a file. There is no way to lock a file from a user who has the rights to use it.

You can either create user groups/roles on SQL Server to segregate the execution depending on your needs/usage factors. To me, there is no straight forward way of locking a file from multiple executions. Sorry!