After creating an SSIS package in SQL server2005, you want to create a task and run it regularly.
At this time, you may encounter errors.
The reason is:
Sql2005 is very different from SQL2000. In SQL2000, you can create and execute tasks without any problems.
In sql2005, you need to run the task through the security layer.
SQL task running environment:
1. The task execution account must use the following roles: SysAdmin, sqlagentuserrole, sqlagentreaderrole, and sqlagentoperatorrole ".
2. The task must run under the Proxy account.
3. It is recommended that the task execution account be used to create an SSIS package and make sure that this task execution account has the permission to run the SSIS package.
Steps:
We use Server Management studio to execute the following tasks and you need to log on with the SA user.
1. Create an execution account
Create a "devlogin" user login, enter the password, you can select the target database (default master)
Select "SysAdmin" as the server role"
User ing: Your target database
MSDB Database: Check whether the following roles are included: sqlagentuserrole, sqlagentreaderrole, and sqlagentoperatorrole.
2. Create a proxy account and bind it to the execution account.
Below are the specificCodeAnd run it in the query analyzer.
Use master
-- Create creden to identify the Windows account and password
Create credential [mycredential] with identity = 'yourdomain \ mywindowaccount', secret = 'windowloginpassword'
Use MSDB
Sp_add_proxy @ proxy_name = 'myproxy', @ credential_name = 'mycredential'
Sp_grant_login_to_proxy @ login_name = 'devlogin', @ proxy_name = 'myproxy'
Sp_grant_proxy_to_subsystem @ proxy_name = 'myproxy', @ subsystem_name = 'ssis'
3. Create an SSIS package.
4. Create a task, schedule and execute the task.
Step Type: SSIS package
Use a proxy account: myproxy.