How to run a SQL Server 2005 Integration Services package as a SQL Server Agent job step

View products that this article applies to.

SUMMARY

This article describes how to run a Microsoft SQL Server 2005 Integration Services (SSIS) package as a SQL Server Agent job step.

Back to the top

MORE INFORMATION

To run an Integration Services package as a SQL Server Agent job step, use one of the following methods.

Back to the top

Method 1

Create a SQL Server Agent job step that runs the package. When you create the job step, click SQL Agent Service Account in the Run as list in the New Job Step dialog box.

Note You must be a member of the sysadmin fixed server role to specify SQL Agent Service Account for the job step.
Grant the appropriate permissions for the SQL Server Agent service account so that the SQL Server Agent service account meets the security context requirement for the package.

Note These permissions are required for the package to access the necessary resources.

Back to the top

Method 2

Create a Microsoft Windows user account that meets the security context requirement for the package.
Create a credential in an instance of SQL Server 2005.

Note A credential is a record that contains the authentication information. The authentication information is needed to connect to a resource that is outside SQL Server.
Create a SQL Server Agent proxy in the instance of SQL Server 2005. When you create the SQL Server Agent proxy, perform the following operations for the SQL Server Agent proxy in the New Proxy Account dialog box:
- In the Credential name box, click the credential that you created in step 2.
- Under Active to the following subsystems, click to select the SQL Server Integration Services Package check box.
Note SQL Server Agent proxies define the security context for a job step. SQL Server Agent proxies use credentials to store information about Windows user accounts. The user who is specified in the credential must have the "Log on as a batch job" permission on the computer that is running SQL Server 2005. You can grant the "Log on as a batch job" permission to a user in the Local Group Policy Editor.
Create a SQL Server Agent job step that runs the package. When you create the job step, click the SQL Server Agent proxy that you created in step 3 for the job step in the Run as list in the New Job Step dialog box.

After you schedule the package, SQL Server Agent may not run the package as a job step successfully. However, if you run this package outside SQL Server Agent, the package can complete successfully. If this problem occurs, click the following article number to view the article in the Microsoft Knowledge Base to troubleshoot the problem:

918760 (http://support.microsoft.com/kb/918760/ ) An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

Back to the top

REFERENCES

For more information about how to create a credential in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms190703.aspx (http://msdn2.microsoft.com/en-us/library/ms190703.aspx)

For more information about how to create a SQL Server Agent proxy in SQL Server 2005, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms190698.aspx (http://msdn2.microsoft.com/en-us/library/ms190698.aspx)

For more information about how to create a job step that runs a package in SQL Server 2005, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms139805.aspx (http://msdn2.microsoft.com/en-us/library/ms139805.aspx)

For more information about SQL Server Agent proxies, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms189064.aspx (http://msdn2.microsoft.com/en-us/library/ms189064.aspx)

For more information about how to schedule a SQL Server 2005 Integration Services package by using the 64-bit SQL Server Agent, click the following article number to view the article in the Microsoft Knowledge Base:

934653 (http://support.microsoft.com/kb/934653/ ) How to use the 64-bit SQL Server Agent to schedule a SQL Server 2005 Integration Services package

For more information about the "Log on as a batch job" permission, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms813942.aspx (http://msdn2.microsoft.com/en-us/library/ms813942.aspx)

Back to the top