Setup and Execute an SSIS package as a SQL Server Job

SQL Server 2005 is different than SQL Server 2000. We now have to deal with a new security layer that just wasn’t there before. Therefore, things on the server have to be setup a bit differently these days.

Let’s walk through the execution/job flow:

 The account that will be used to run and setup the job needs the roles of sysadmin, SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole

 The job needs to be run established to run under a Proxy account

 Is always better to create the SSIS package using the account that will eventually run it – but that is not the real world. But in doing so, you are sure the job executor account will always have the right to run this SSIS package.
Let’s lay out the procedure needed

If you can, log in as SA. If not, then be prepared to grovel and beg. You need an account in order to do your work.
I. Create the job control account

Highlight Security->New Login, enter a new login name SUCH AS devlogin or nukeem, type your new password, and of course, your default database will most likely be your target database, but not always.

In Server Roles: check “sysadmin”

User mapping: the target database

Msdb database: Make sure to include SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole

Then click OK

II. Create SQL proxy account and associate proxy account with job executor account

Open up a new query window and run the following:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

GO

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'

GO

III. Create SSIS package

In MS SQL Server Business Intelligence Development Studio, you use job executor account devlogin to create the SSIS package (DTS) and make sure you can execute this package in SQL Server Business Intelligence Development Studio. Compile/build this package.



IV. Create the job, schedule the job and run the job

In the SQL Server Management Studio, highlight the

1. SQL Server Agent -> Start.

2. Highlight Job ->New Job…, name it , SSISJobName.

A steps category will appear. Under the Steps list enter, New Step, name it, Step1 or whatever,

Type: SQL Server Integration Service Package

Run as: myProxy

Package source: File System or SQL server, which ever is correct

Browse to select your package file xxx.dtsx in the appropriate directory

Click Ok

Schedule your job and enable it

Now the job can be executed.





To Execute a job from a stored proc:



--Start Job

EXEC msdb.dbo.sp_start_job N'Weekly Accounts Payable Transaction History'



--Stop Job

EXEC msdb.dbo.sp_stop_job N'NightlyInventoryFeed'



--Info abt Job

EXEC msdb.dbo.sp_help_job

@job_name = N'FinanceBackup',

@job_aspect = N'ALL' ;



GO