Scheduling packages via SQL Server Agent

You can schedule the execution of DTLoggedExec using SQL Server Agent, so that your packages can be executed at scheduled interval.

To schedule a package you'll normally create a new job with a step of type "SQL Server Integration Services Package", as the picture below shows:

What this step really do behind the scenes is a simple invocation of DTExec executable. This means that all we have to do to execute our packages using DTLoggedExec is just use another job step type, that allows us to run any executable we want.

So, here's how to configure a job step so that it can run DTLoggedExec.

1) Select "Operating System (CmdExec)" type.

2) Use the correct proxy account for you. To have detailed information on how to setup and use proxy account read this article carefully: An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.

3) Set the command line so that it will invoke DTLoggedExec with all the paramters you need, just as you would do if you're calling DTLoggedExec from command prompt

This is the command line we're using for this example:

"C\DTLoggedExec\bin\Any\DTLoggedExec.exe" /FILE:"C:\Work\SSIS Logging\DTLoggedExec\TestPackages\TestPackages\Test Package 6.dtsx" 
/LE:E /PDF /PP:"C:\Work\SSIS Logging\DTLoggedExec\DataFlowProfile" /PFN:"TestPackage6_{date}_{time}"

4) Using the default ConsoleLogProvider you can direct all the logged information to a file, using the "Advanced Tab", specifing a value for the "Output file" option

This will allow you to have the detailed log information of the package execution, so that you can use them to check for errors if they happens. To have more information on what you'll find in this log, read the Using the Console Log Provider page.

5) Finished, just say ok and save the job.