Sign in Subscribe

SSIS: Running Packages in SQL Server Agent

Arie D. Jones

• 2 min read

At my talk today I ran into a couple of individuals that asked me the same question. “Why can’t I run this package as a scheduled job?”. I run across this many times in my consulting because there is a lot of documentation on deployment out there but none of it really seems to click with people. Basically, it boils down to the 4 following points:

  1. When you run an SSIS package either from the BIDS interface or by actively clicking on the package file you are running under YOUR account. This means that the package has access to all of the objects both local and network that you have access to.
  2. When you run an SSIS package or any other program from a SQL Server Agent job, it will run in whatever execution context that the step is set to. Normally, there will be the SQL Server Agent’s service account(which by default is the Local System Account) but could also be a proxy account. If you’re unsure of how to run the SQL Server, check out Microsoft SQL Server Consulting to get some help.
  3. Most of your packages will need access both to local and remote objects.

So what do we need to do? First of all, you need to set up an account with all of the permissions that your packages will need. This needs to be a domain account so that you can easily access remote objects on the network. Next either do one of the following ..

  1. Set the domain account to be the SQL Server Agent account. See Microsoft’s article here…
  2. Set the account up to be a SQL Server Agent Proxy account. See the Microsoft’s article here….

Those that attended the event remember that I talked about the issue with encryption of sensitive information within the package and how I like to use the ServerStorage method. If you chose this method then you are definitely putting your package into SQL Server, it will be stored in the sysdtspackages90 table in the msdb database, and access will be controlled by the following database roles(*located within the msdb database!) :

  1. db_dtsadmin: admin rights to packages
  2. db_dtsltduser: rights to only execute packages that the user has been specifically given access to
  3. db_dtsoperator: run as well as backup and restore packages.

Hopefully, this bit of information can save you some headaches when you get arounf to deploying your packages.

Cheers,
AJ

Powered by ScribeFire.

Sign up for more like this.

Enter your email
Subscribe