How to: Automate SSIS Package Execution by Using the SQL Server Agent (SQL Server Video)
Applies to: Microsoft SQL Server Integration Services |
Authors: Carla Sabotta, Microsoft Corporation |
Length: 00:10:11 Size: 8.22 MB Type: WMV |
Watch this Video on TechNet, to watch, share, and download the video in multiple formats. |
|
|
Video Summary
This video demonstrates the following:
How to create a proxy account that runs an Integration Services package as a job step in SQL Server Agent.
How to create the job and the job step.
How to schedule the running of the job.
Video Transcript
Video Time-Stamp |
Audio |
|---|---|
00:00 |
Hello, my name is Carla Sabotta. I write documentation for the Microsoft SQL Server Integration Services product. In this video, I’m going to show you how to configure the SQL Server Agent to automate the execution of an SQL Server Integration Services package. You’ll learn how to create a proxy account that runs the package as a SQL Server Agent job step, create the job and the job step, and create a schedule for running the job. The first step to configuring SQL Server Agent to run a package is to open SQL Server Management Studio and connect to the SQL Server database engine. The account that runs the package as a job step must have all the same permissions as an account that runs the package directly, in order to access any external resources that the package needs to access. Otherwise, the job will not execute. You can use the existing SQL Server Agent Service Account or create a SQL Server Agent proxy account to run the package as a job step. In this demonstration, we'll create a proxy account. |
01:13 |
To create a proxy account, you must be a member of the sysadmin fixed server role. Or, you must be a member of the SQLAgentOperatorRole, SQLAgentReaderRole, or SQLAgentUserRole, in the msdb database. You create a proxy account by running a Transact-SQL query or by using the New Proxy Account dialog box in SQL Server Management Studio. We’ll use the New Proxy Account dialog box. On the General page, you specify the name and credential for the new proxy account. We’ll name the account, Package proxy and select an existing credential called, User1, that contains the authentication information. Keep in mind that the selected credential must enable SQL Server Agent to run the job as the account that created the package or as an account that has the required permissions. You also need to specify the subsystem for which the proxy is enabled. Each subsystem listed here is represented by a job step type. Because we’ll select the SQL Server Integration Services Package type when we configure the job step, for the proxy account we select the SQL Server Integration Services Package subsystem. The proxy description is optional. On the Principals page, you can add or remove roles to grant access to the proxy account. Members of the sysadmin fixed server role have automatic access. The User1 credential we specified for the proxy account is listed under the Credentials node in Object Explorer. |
03:27 |
You can create a new credential by running a Transact-SQL query or by using the New Credentials dialog box. Now that you’ve created the proxy account, you’re ready to create the job and add a step for the package you want to run. To run multiple packages, you would create a step for each package. Before you can create a job, SQL Server Agent Service must be running. If this icon appears next to SQL Server Agent in the Object Explorer tree, the service is disabled. In this case, right-click the SQL Server Agent node to re-start the service. |
04:17 |
You create a job and job step by calling a stored procedure or by using the New Job dialog box. We’ll use the New Job dialog box. First, you configure the job properties on the General page of the dialog box. You need to specify the name of the job, and confirm that the owner and category settings are accurate. And, you have the option of providing a job description. We’ll name the job, Integration Services Package, and accept the other default settings. And, we’ll select the Enabled checkbox to ensure that the job can be scheduled. |
05:01 |
Next, you add a job step on the Steps page and then configure the properties. We’ll name the job step, Basic package. Because we’re running a package using a version of the dtexec utility automatically invoked by the system, we’ll select the SQL Server Integration Services Package type. When the job runs, the 32-bit or the 64-bit version of the utility is automatically invoked, depending on the computer processor, and the versions of SQL Server and SQL Server Agent that are running on the computer. In this demonstration, 32-bit versions of SQL Server Agent and SQL Server are running on a 32-bit computer. Therefore, the 32-bit version of the utility is invoked. You have the option of invoking the 32-bit version of the utility on a 64-bit computer, with 64-bit versions of SQL Server and SQL Server Agent installed. You simply select the Use 32 bit runtime option on the Execution options tab. This is a new feature in SQL Server 2008. In the Run as list, we’ll select the proxy account we created earlier, which runs the package as a SQL Server Agent job step. Next, on the General tab, you select the package source, which can be SQL Server, the SSIS Package Store, or the File System. In this demonstration, the package is stored in the File System. |
06:54 |
You have the option of specifying a number of command line options for the job step, such as adding configuration files, updating data source connection strings, and adding log providers. We’ll add a log provider to write log entries for events to an SQL Server database by using a connection manager in the package. The DestinationConnectionOLEDB connection manager connects to the AdventureWorks2008 SQL Server database. |
07:38 |
Now that you’ve created the SQL Server Agent job and added a job step, you’re ready to schedule the job. You can select an existing schedule or create a new one. You need to specify the schedule name, and when and how often the job will run. We’ll name the schedule, Integration Services Package Schedule, and configure the job to run once a day at 5pm until the end of the month. To enable the schedule, make sure the Enabled check box is selected. The system will now automatically execute the package on a set schedule, using the proxy account we created to run the job. |
08:59 |
You can also run a job anytime outside the time frame set by the schedule, which we’ll do now. Because the job we created contains only one job step, the job starts immediately; otherwise, we would need to select which step to start the job at. As you can see, the job successfully executed the package. You can use SQL Server Agent to execute a package that is stored on your local machine, as we did in this demonstration, or to execute a package that is stored on another machine. Keep in mind that regardless of where the package is stored, it will run on the same machine as the SQL Server Agent that invokes the package. This video demonstrated how to create a proxy account that runs a package as a SQL Server Agent job step, create the job and the job step, and create a schedule for running the job. Thank you for watching this video. We hope that you have found this of value, and will return to the Web site to view other Microsoft SQL Server videos. |