Case
An administrator of a shared Microsoft SQL Server Integration Services machine wants to run the various packages with different user accounts via the scheduler of SQL Server Agent. He has created separate users for each department and gave them all only rights for there own windows folders.
Solution
1) Goto Microsoft SQL Server Management Studio (SSMS) and create a new Credential (under Security).
2) Give the credential a suitable name and select the applicable windows user and enter the password
3) Create a new Proxy account (under SQL Server Agent).
4) Give the proxy a suitable name and description and select the applicable Credential. After that select SQL Server Integration Services Package (that's the only thing this proxy may do).
5) Create a new Job (under SQL Server Agent).
6) Give the Job a suitable name on the General tab. Goto the Steps tab and create a new step. Give the step a suitable name. Select SQL Server Integration Services Package as Type and the most important: select your Proxy account under Run as. And of course select the package you want to run. There are a lot more options on this screen. Feel free to try them out.
7) Now add a new Schedule on the Schedules tab. Give it a name and select the desired schedule. This one starts each night at 0:00.
Repeat all the steps for all the departments and they won't bother each other.
An administrator of a shared Microsoft SQL Server Integration Services machine wants to run the various packages with different user accounts via the scheduler of SQL Server Agent. He has created separate users for each department and gave them all only rights for there own windows folders.
Solution
1) Goto Microsoft SQL Server Management Studio (SSMS) and create a new Credential (under Security).
 |
| SSMS, Security, Credentials |
2) Give the credential a suitable name and select the applicable windows user and enter the password
 |
| New credential |
3) Create a new Proxy account (under SQL Server Agent).
 |
| SSMS, SQL Server Agent, Proxies |
4) Give the proxy a suitable name and description and select the applicable Credential. After that select SQL Server Integration Services Package (that's the only thing this proxy may do).
 |
| New Proxy account |
5) Create a new Job (under SQL Server Agent).
 |
| SSMS, SQL Server Agent, Jobs |
6) Give the Job a suitable name on the General tab. Goto the Steps tab and create a new step. Give the step a suitable name. Select SQL Server Integration Services Package as Type and the most important: select your Proxy account under Run as. And of course select the package you want to run. There are a lot more options on this screen. Feel free to try them out.
 |
| New Job, New Step |
7) Now add a new Schedule on the Schedules tab. Give it a name and select the desired schedule. This one starts each night at 0:00.
 |
| Each night at 0:00 |
Repeat all the steps for all the departments and they won't bother each other.
Please use the SSIS MSDN forum
for general SSIS questions that are not about this post. I'm a regular
reader of that forum and will gladly answer those questions over there.
