SSIS deployment to SQL Job

Microsoft BI Series Essay-SSIS Foundation-Deploy SQL Job Introduction

In the previous blog, we described how to implement project deployment for SSIS and accelerate deployment with the environment with SSIS parameters, see Microsoft BI series Essay-SSIS Foundation-parameters and environment. The article at the end mentions the deployment of SSIS to the SQL Job. This article describes how to use SQL job to plan the running tasks for an SSIS package.

about SQL Job

SQL Server Agent is a task planner and alert manager that, in real-world applications and environments, can define periodic activities as a single task, with the help of SQL Server Agent to run automatically.

preparatory work

First of all, in the previous blog post on the SSIS Project deployment model and the Microsoft BI series essay-SSIS Foundation-parameters and environment, we deployed an SSIS project to the SSISDB directory and configured it with environment parameters. Is the structure diagram after completing the above steps

Create SQL Job

Right click on jobs, select "New Job", the system will open the job creation window.

Basic Information

The basic information page is primarily the job's name, owner, category, and description.

Add Step

The next step is to add the steps for the job, where we need to give the name of the step, select the type and the account to execute.

In the first step we select the type of SQL Server Integration Services package, which indicates that this step is based on SSIS packages.

In the second step we choose to execute the account, here is the SQL Server Agent account, he is a SQL Server built-in accounts, if you have other accounts can also be used. But pay attention to his permissions issues, such as the SQL Server Agent account does not have read and write permissions to the General disk folder, if you have a package file read and write, you need to write permissions on the folder.

The third cloth is to select the server where the package resides and the location of the specific package.

The fourth step is to configure the parameters to use the environment variables that were previously configured, which corresponds to the environment variables in the previous SSISDB directory.

Click OK to save the steps.

set up an execution plan

Set the type of schedule, the execution period, and the start time. This step is relatively straightforward.

Next, set up alerts, notifications, and targets. If you set up the handling of errors and alerts in the package, create a mail notification task, do not set up separately, using the system default is possible. If you need this information, you can configure it yourself.

Click OK, OK, the task creation is complete. You can see that our new job appears under the Jobs directory.

Scripting Research

Create the script for this job to look at:

/** * * * * object:step [coposorderprocessing] Script date:8/28/2015 10:32:02 AM * * * **/EXEC @ReturnCode =Msdb.dbo.sp_add_jobstep@job_id=@jobId,@step_name=N'coposorderprocessing',         @step_id=1,         @cmdexec_success_code=0,         @on_success_action=1,         @on_success_step_id=0,         @on_fail_action=2,         @on_fail_step_id=0,         @retry_attempts=0,         @retry_interval=0,         @os_run_priority=0,@subsystem=N'SSIS',         @command=N'/isserver "\" \ssisdb\edcssisprocessing\edcssisprocessing\coposorderprocessing.dtsx\ ""/SERVER "\" PRCSGI12071D \mssqlserver12\ ""/envreference 1/par "\" $ServerOption:: Logging_level (Int16) \ ""; 1/par "\" $ServerOption:: SYNCHRONIZED (Boolean) \ ""; True/callerinfo sqlagent/reporting E',         @database_name=N'Master',         @flags=0

In the above code, focus on the content of @command.

/isserver "\" \ssisdb\edcssisprocessing\edcssisprocessing\coposorderprocessing.dtsx\ ""--specifies that a package that is deployed on SSIS is executed./SERVER "\" prcsgi12071d\mssqlserver12\ ""--specify to get that package from that server/Envreference1--Specify environment variables to use when the package is run/Par "\" $ServerOption:: Logging_level (Int16) \ "";1--Logging Record level/Par "\" $ServerOption:: SYNCHRONIZED (Boolean) \ ""; True--run synchronously, meaning that if the instance is running, it cannot be executed repeatedly/Callerinfo SQLAGENT--This task is to be called by the SQL Agent ./REPORTING E--only error messages will be reported

It seems that everything is here. All package information, environment variables, execution parameters are clearly stated in this command.

Well, execute it, and see if it's an effect on ssisdb inside, and look at our [internal]. Does the [Executable_statistics] table record the running information?

Summary

This article explains in detail how to use SQL Agent to plan SSIS package execution, by combining SSIS parameters and environment variables to achieve a seamless integration between them, greatly facilitates the deployment work.

SSIS deployment to SQL Job