This post from / 来源
http://www.colestock.com/blogs/2008/06/how-to-create-and-schedule-ssis-job-in.html
This post covers how to create and schedule a SSIS job on SQL Server 2005.
Thebiggest challenge in doing this has to do more with the convalutedsecurity setup, than with the SSIS package creation and/or scheduling.
The first step is to create the necessary security objects:
ssis_security_setup.sql
Create a Login who will own the job
创建一个新的帐号给ssis用
USE [master]
GO
CREATE LOGIN [ssis_usr] WITH PASSWORD=N'password', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
Grant the sysadmin role to the previously created login
再创建帐号的角色
sp_addsrvrolemember @loginame = N'ssis_usr', @rolename = N'sysadmin'
GO
Create a user in the msdb system database for the login, creating the mapping
继续创建
USE [msdb]
GO
CREATE USER [ssis_usr] FOR LOGIN [ssis_usr]
GO
Grant the following roles to the user
继续创建
sp_addrolemember N'SQLAgentReaderRole', N'ssis_usr';
GO
sp_addrolemember N'SQLAgentOperatorRole', N'ssis_usr'
GO
sp_addrolemember N'SQLAgentUserRole', N'ssis_usr'
GO
Create a credential to be used by the proxy - the proxy will execute the SSIS package within the job
继续创建
USE [master]
GO
CREATE CREDENTIAL [ssis_cred] WITH IDENTITY = N'PRIVATE-E3A52F5\James', SECRET='password';
GO
Create the proxy, mapping to the credential and provide access to the SSIS subsystem
继续创建
USE [msdb]
GO
sp_add_proxy @proxy_name=N'ssis_prxy', @credential_name=N'ssis_cred';
GO
sp_grant_proxy_to_subsystem @proxy_name=N'ssis_prxy', @subsystem_name=N'SSIS';
GO
Now you are ready to create a sample SSIS package.
现在你已准备好了,开始执行SSIS DTS包的做功。
PS:如果你的SSIS包写好了,这步骤跳。
I launch the SQL Server Business Intelligence Development Studio via All Programs -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio
I create a project named sample_ssis, which consists of a Transfer Database Task, which refreshes a database from one SQL Server instance to another:
sample_ssis.dstx

Task Properties

Once the sample SSIS package is created, import into the msdb system database via Integration Services:
接下来把你的SSIS DTS放进Integration Services跑。
Login to Integration Services

Import the SSIS Package from the File System

Login to the Database Engine using ssis_usr

Create a SQL Server Agent Job

Create a Step within the Job run by ssis_prxy

Create a Schedule for the Job

Monitor the Job

You should see something similar to the aforementioned if your job executed successfully.
对不起,华语程度有限,不很厉害翻译。
不需要流利的英文也能明白。
本人已经成功执行了,祝你好运。 |