How to Create and Schedule a SSIS Job in SQL Server 2005

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

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.

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:

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.

Labels: SQL Server