SQL Server 2014 SSIS – Missing Tabs in Job Step Properties

sql server 2014ssis

I am going to echo another question almost exactly, only my question has a different objective:

In Job Step > Properties, only see two tabs: Package and Configuration

I am trying to set a job step that executes a package requiring a package password, since the package protection level is set to EncryptSensitiveWithPassword. The job is running on SQL Server 2014. I see all kinds of examples of how to do this, where the job step properties page has 9 tabs (Set Values, Verification, Command Line, General, Configurations, Command Files, Data Sources, Execution Options and Logging). Command Line allows me to enter the password (with the /DECRYPT command line option).

When I look at my job step, logged in to the server as an Admin, running SSMS as administrator, all I see are two tabs: Package and Configuration. I deployed the packages using the Project Deployment method. How do I get those other tabs to show up?

(My ultimate goal is to give the SQL Agent who runs the job access to a MySQL DB password, and I understand setting the package protection level to EncryptSensistiveWithPassword is the correct way to do that, but I cannot find another way to provide the package password.)

Best Answer

In the job step properties after selecting SQL Server Integration Services Package as the type, switch Package source to SSIS Package Store to get the 9 tabs. SSIS Catalog is the default and only has 2 tabs.

SSIS Package Store:

SSIS Catalog (the default):

Related Solutions

SQL Server 2008 – SSIS Package Configurations Ignored

You must take into account that when running by BIDS the package will take first the variable value from the config file, and only if the config file doesn't exist, it will throw a warning and the value will be taken from the package.

Now, the situation in command line is a bit different. You can have the following situations:

run the package in cmd line without any config file chosen:
```
dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx"
```
- if the original config file (let's name it Prod) doesn't exist in the same path defined in the metadata of the package, values from inside the package are used and you'll just receive a warning that config file is missing;
- if the original config file exists and is valid, then values from the config file will be used (inner values will be bypassed);
run the package in cmd line without any config file chosen, but with variable set in the call:
```
dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /SET \Package.Variables[checkMe];"outside the package in cmd line"
```
- if the original config file doesn't exist, then the value is taken from the /SET package call;
- if the original config file does exist, then the value is taken from the config file and even the /SET is ignored (this is used only in the case above);
run the package in cmd line with a new config file (let's say DEV instead Prod):
```
dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig"
```
- if the new config (Dev) file exists, and old (Prod) doesn't, then values from it are used;
- if both the Dev and Prod config file exist, then only values from Prod is used (DEV is bypassed even if specified in command line call);

run the package in cmd line with a new config file and a SET statement in the call:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig" /SET \Package.Variables[checkMe];"outside the package in cmd line - DEV config"

if both config files exist, Prod will be used, all others ignored, even the SET;
if no config file exists, SET value will be used;

So, in short, if you want to use a new config file you'll have to rename/move the old one and call the package with /configFile. If that's not enough and want to override even new config file, then use the /SET variable. Or you can bypass any config file and just use /SET statements in the batch call.

Hopefully that will shed some light in your possibilities.

Sql-server – In Job Step > Properties, only see two tabs: Package and Configuration

It's on the Configuration which then has 3 subtabs: Parameters, Connection Managers and Advanced

enter image description here

Advanced allows you to specify 32 bit vs 64 bit runtime, change the logging levels, dump on errors and override package properties.

enter image description here

Best Answer

Related Solutions

SQL Server 2008 – SSIS Package Configurations Ignored

Sql-server – In Job Step > Properties, only see two tabs: Package and Configuration

Related Question