One issue with SSIS packages is that they run fine when executed manually, but when scheduled as a SQL Agent job, they do not run, or have problems.
This can be because the SQL Agent runs jobs under its own account by default and may not have the same permissions as the account used when they are created and executed manually.
The answer is to use credentials and proxies to impersonate an account with the correct permissions.
So having tested that the SSIS package works correctly when run manually under a certain account do the following:
1. SQL server Management Studio.
Security - Credentials- Create New Credential.
Give it a name.
Fill identity column with domain\account used to test package successfully.
Password of that account
2. SQLServerAgent-Proxies.
Create a new proxy.
Give it a name.
Credential should be the one which you created previously in step 1
In subsystems. Check Sql Server Integration Servive Package.
3. Now when you create a job step, in the Run As box, change it from 'SQL Agent Service account' to the proxy created in Step 2.
Example:
No comments:
Post a Comment