Friends,
Most of the times when you design a package, it works as expected in design environment but the same fails when you configure it in Job(Sql Server Agent). the reason for the above problem is you have connections that use Windows Authentication. At design time, the package uses your credentials, and when you schedule the package, it uses the SQL Server Agent service account by default. This Agent account may not have access to a file share or database server that is necessary to successfully run the package. The solution for that problem is using PROXY ACCOUNTS.
Let’s see the steps involved in creating Proxy Account.
- Creating Credential
- Assigning credential
Creating Credential :
- To create a credential, Connect to SSMS and rightclick on Security folder and select “Credential” as shown below.
- Give some name and I have given “Test” in this case. The credential will allow users to temporarily gain administrator access. For the Identity property, type the name of an administrator account or an account with higher rights. In this case I have given my details as shown below.
- Click on OK. A credential will be created with the name given.
Assigning credential : Now let’s see the steps involved in assigning a credential.
- Go to Sql Server Agent Tree.
- Right click on Proxies and select option “New Proxy” as shown below.
- When the wizard opens,Type Test Proxy for the Proxy Name property, and Test(which we created in last step) as the Credential Name. Check SQL Server Integration Services Package for the subsystem type allowed to use this proxy.
- Now click OK to save the proxy. Now you can use the proxy created to run SSIS packages by configuring to Sql Server Agent Jobs. You will be able to use the new proxy by selecting the Admin Access Proxy from the Run As drop-down box. Any connections that use Windows Authentication will then use the proxy account instead of the standard account.
That’s it .. Hope this solves your issue .. 🙂
Regards,
Roopesh Babu V
