How to Create and Schedule a SSIS package ( DTS ) Job in SQL Server 2005 / [教学]如何实现定时执行SSIS程序包 DTS job （中 英 文）

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.
The biggest challenge in doing this has to do more with the convaluted security 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.


对不起，华语程度有限，不很厉害翻译。
不需要流利的英文也能明白。
本人已经成功执行了，祝你好运。