4

How SQL Server maintenance plans work in relation with Jobs and DTExec?

I am specially interested in the maintenance plan's local connection which seems to be passed somehow to dtexec.

I have read some docs but none of them is clear enough. As I have understood it: the MP creates a SSIS package as well as a job... and the job in turn invokes the external application dtexec... which executes the SSIS package. This package has to access the SQL Server and to get that it uses the account provided by the "local connection" parameter of the maintenance plan.

Is my understanding right?

Some reference to MS official detailed documentation would be welcome.

Dale K
  • 25,246
  • 15
  • 42
  • 71
IgnacioJ
  • 105
  • 11
  • 1
    Yes! You can list all "SSIS steps" by executing SELECT job.*,steps.* FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id WHERE job.enabled = 1 AND subsystem = 'SSIS' –  Apr 29 '19 at 10:08

1 Answers1

0

Maintenance plans are saved in form of SSIS packages in the MSDB database. You can access them by connecting to the Integration Services from the SQL Server Management Studio. You can refer to the following link for more information:

In addition you can retrieve information about these packages by querying the tables and views located in msdb database:

Ever SQL job execte the SSIS package by passing connections and other values as parameters.

Hadi
  • 36,233
  • 13
  • 65
  • 124