Home > SQL Server, SSIS > Execute SSIS package from SQL Server Job

Execute SSIS package from SQL Server Job

2014/01/15

In order to execute a SSIS package from a SQL Server job there are some security issues.

You develop the package in Sql Server Data Tools (or the old SQL Server Business Intelligence Development Studio) and it works; in the package for example there are some trasformations from CSV files to an SQL Server server instance , and in the package the Connection Manager for SQL Server uses the SQL Server authentication.

The first step that seems logical to try is to insert a step in the SQL Server job as this:


But the job fails ,and in the job log is written :Failed to decrypt protected XML node “DTS:Password”

The problem is that in the SSIS project all sensitive informations are available , instead when the package is executed from the SQL job this uses the dtsexec utility, for which you need to pass in the command line the credentials.

If the security is a concern, an approach can be to open the SSIS project and in the project properties (open the dtsx on Control Flow visual editor, clic on the empty space, press F4) , where there is the security section


By default the ProtectionLevel is EncryptSensitiveWithUserKey , but in this case we use EncryptSensitiveWithPassword.

Then clicking on PackagePassword we are requested for the password:


Which is important to remember, the password will be requested opening the solution:


The package will be opened if you press Cancel, but is not possible to save changes:


At this point , going on our SQL Job definitions we will be required for the password by clicking on the “Command Line” tab:


We see an dtexec command line, in this case by default

/FILE “E:\PkgSSISHecPool\PkgSSISHecPool\PkgSSISHecPool\HecPool.dtsx” /DECRYPT /CHECKPOINTING OFF /REPORTING E

Clicking on “Edit the command line manually” we can change as


Save , and the package step works when called from the SQL Server job.

Advertisements
Categories: SQL Server, SSIS
%d bloggers like this: