Command Line


BI xPress Server also comes with the option to install the Auditing Framework command line tool, which utilizes the AuditingFramework.CommandLine.exe located within the Auditing Framework command line directory of the Workbench Server installation. By default, this is can be found in the following location, though this may change depending on the installation location set during install time:


C:\Program Files (x86)\Pragmatic Works\Workbench Server\Auditing Framework Command Line\AuditingFramework.CommandLine.exe


Any framework (such as Scheduled Tasks, TFS Build Server, Microsoft Systems Center) that is capable of starting an application and passing parameters through a response file or command line arguments will be capable of utilizing the Auditing Framework command line to apply/re-apply or remove the Auditing Framework.


When you use AuditingFramework.CommandLine.exe to apply the Auditing Framework to packages, you can include several switches to specify various options to be included within Auditing Framework.



AuditingFramework.CommandLine.exe [<Action>] {<Items>} <Item Information> [<Options>] [<Configuration Information>] [<Connection Information>] [<Custom Variable Logging>]



The Auditing Framework can be used in the traditional sense by directly inputting each desired switch into the Command Prompt, or it can be used to reference a response file that contains all desired switches.


Response File

Switch

Description

@file

Insert all command-line switches to be executed against the Auditing Framework command line tool from a text file. The response file location must be surrounded by " (double-quotes) if it contains a space or special characters.


Switch Arguments

To introduce a parameter argument to the AuditingFramework.CommandLine.exe, use a / (slash) followed immediately by either the parameter name or the parameter abbreviation. If the Parameter requires a value, a : (colon) or an = (equal) will be used to separate the parameter argument with the parameter value and must be surrounded by " (double-quotes) if the value contains a space or special character(s).


Below is a list of all Parameter Arguments grouped by their specific purpose:


Action

Switch

Short Form

Description

/add

/a

This action will add the Auditing Framework to the specified item(s). If no Action switch is specified, AuditingFramework.CommandLine.exe will, by default, use the add switch.

/remove

/r

This action will remove the Auditing Framework from the specified item(s).


Items

Switch

Short Form

Description

[/item:]values

[/i:]values

The fully qualified location(s) of the package(s), project(s), or solution(s). This must include the full package name including the file extension if the location is FileSystem.  If the location parameter is SqlServer or SsisStore, this location must include the full path to a package and start with a backslash "\".


If specifying multiple targets, all items must be from the same location (FileSystem, SqlServer, or SsisStore) and use spaces in between values, as the following example shows:


/item:"\SSIS\MasterPackage" "\SSIS\ETLPackage"


NOTICE: SQL 2005 packages must have the auditing framework applied or removed separate from SQL 2012 or SQL 2014 packages

/itemPassword:value

/ip:value

The password for the SSIS project or package(s) that are being modified.  The default value is no password will be applied to the item(s). If a password is present, this password is applied to each package.


Item Information

Switch

Short Form

Description

/location:value

/l:value

The location to all specified SSIS Item(s) referenced by the item switch. Users can specify the following values:

  FileSystem.

  SqlServer. Setting the location switch to SqlServer requires the locationVersion switch to be set.

  SsisStore. Setting the location switch to SsisStore requires the locationVersion switch to be set. By default, only administrators have access to the SSIS Package Store and the command prompt used to execute this process must be running with administrative privileges.

/locationVersion:value

/lver:value

Required when setting the location switch to either SqlServer or SsisStore. The locationVersion switch directs the application to use a specific SSIS runtime to process the request. Users can specify the following values:

  Sql2005

  Sql2008

  Sql2012

  Sql2014

  Sql2016

  Sql2017

/dataSource:value

/ds:value

Required when setting the location switch to either SqlServer or SsisStore. The dataSource switch directs the application to the server name (and instance name if using SqlServer) where the SSIS items are located.

/userName:value

/u:value

If provided when setting the location switch to either SqlServer or SsisStore, the userName switch (alongside the password switch) sets Integrated Authentication to False and specifies the user name to be used in order to connect.

/password:value

/p:value

If provided when setting the location switch to either SqlServer or SsisStore, the password switch (alongside the userName switch) sets Integrated Authentication to False and specifies the password to be used in order to connect.


Add Only Arguments

The following arguments are to be used alongside the add switch and will be ignored if used with the remove switch:


Options

Switch

Short Form

Description

/scriptLanguage:value

/sl:value

Specifies the script language that will be used when applying the Auditing Framework. The default value for SSIS 2008 and later items is CSharp. This switch will be ignored for SSIS 2005 items. Users can specify the following values:

  CSharp

  VB

/logRowCount:value

/lrc:value

Enables row count logging within Data Flows. The default value is True. Users can specify the following values:

 True

  False

/logRowCountSource:value

/lrcs:value

Enables row count logging for source components found within Data Flows. The logRowCountSource switch will be ignored if the logRowCount switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/logRowCountDestination:value

/lrcd:value

Enables row count logging for destination components found within Data Flows. The logRowCountDestination switch will be ignored if the logRowCount switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/logSqlStatements:value

/lsql:value

Enables logging of SQL Statements. The default value is True. Users can specify the following values:

 True

  False

/logConnectionStrings:value

/lconn:value

Enables logging of connection strings. The default value is True. Users can specify the following values:

 True

  False

/logWarnings:value

/lw:value

Enables logging of warnings issued during package execution. The default value is True. Users can specify the following values:

 True

  False

/logVariables:value

/lv:value

Enables logging of variable value changes during package execution. The default value is True. Users can specify the following values:

 True

  False

/logVariableType:value

/lvt:value

Enables logging of all variable value changes or only the variables used within ForEach tasks during package execution. The logVariableType switch will be ignored if the logVariables switch is set to False. The default value is All. Users can specify the following values:

 All

  ForEach

/logInitialValues:value

/liv:value

Enables logging of initial variable values at the time of applying the Auditing Framework. The logInitialValues switch will be ignored if the logVariables switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/logObjectVariables:value

/lov:value

Enables logging of variables with an Object data type. The logObjectVariables switch will be ignored if the logVariables switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/logTasks:value

/lt:value

Enables logging of each task's start and stop times, which is essential for seeing package execution progress within the Monitoring Console. The default value is True. Users can specify the following values:

 True

  False

/logTaskType:value

/ltt:value

Enables logging of each task's type and parent object. The default value is True. Users can specify the following values:

 True

  False

/continueOnAfDbFailure:value

/coadf:value

Enables the continuation of package execution if there is an error connecting to the BI xPress database. The default value is False. Users can specify the following values:

 True

  False

/doNotReportAfFailure:value

/dnarf: value

Enables the silencing of Auditing Framework failure messages in the Auditing Framework execution history. The default value is False. Users can specify the following values:

 True

  False

/maxIterations:number

/mi:number

Specifies the maximum number of iterations to log within a For or Foreach loops. A value of 0 indicates that all iterations should be logged and only numbers greater than or equal to 0 are allowed.  The default value is 0.


The following example instructs AuditingFramework.CommandLine to only log the first 5 iterations within For and Foreach Loops:


AuditingFramework.CommandLine.exe "C:\SSIS\MasterPackage.dtsx" /l:FileSystem /maxIterations:5

/logDataFlows:value

/ldf:value

Enables the logging of Data Flows. The default value is True. Users can specify the following values:

 True

  False

/logConnsForSrcDest:value

/lcsd:value

Enables the logging of connection details for source and destination components within Data Flows. The logConnsForSrcDest switch will be ignored if the logDataFlow switch is set to False. The default value is True. Users can specify the following values:

 True

  False


Configuration Information

Switch

Short Form

Description

/configType:value

/ct:value

Enables the use of configuration or parameters for externally setting Auditing Framework variables and property values. The use of Package and Project Parameters requires the SSIS Items to be SSIS 2012 or later. In addition, the use of Project Parameters require the SSIS Items to be SSIS project files. The default value is None. Users can specify the following values:

 None

  ConfigurationFile

 PackageParameters

 ProjectParameters

/configOverwrite:value

/co:value

Enables overwriting the configuration or parameters if they already exist. The default value is True. Users can specify the following values:

 True

  False

/configPath:value

/cp:value

When setting the configType switch to ConfigurationFile, the configPath switch directs the application to the directory location to place the configuration file. The directory must exist during application of the Auditing Framework. The configPath switch will be ignored if the configType switch is set to None, PackageParameters, or ProjectParameters. The default value is "C:\SSIS".

/configProjConnMgrOverwrite:

value

/cpcmo:

value

Enables removing and replacing a package-level connection manager with a project-level connection manager when a connection manager with the same name exists in a package. The configProjConnMgrOverwrite switch will be ignored if the configType switch is set to None, ConfigurationFile, or PackageParameters. The default value is True. Users can specify the following values:

 True

  False

/configAuditFramework:value

/ca:value

Enables the inclusion of a configuration entry controlling the ability to enable and disable the Auditing Framework. The configAuditFramework switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:

 True

  False

/configDataFlows:value

/cdf:value

Enables the inclusion of a configuration entry controlling the ability to enable and disable Data Flow Row Count logging and Source Information logging. The configDataFlows switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:

 True

  False

/configWarnings:value

/cw:value

Enables the inclusion of a configuration entry controlling the ability to enable and disable the logging of SSIS Warnings. The configWarnings switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:

 True

  False

/configVarsParamsConns:value

/cvpc:value

Enables the inclusion of a configuration entry controlling the ability to enable and disable the logging of connection strings and variable logging. The configVarsParamsConns switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:

 True

  False

/configVarsConnsFiltering:value

/cvcf:value

Enables the inclusion of a configuration entry controlling the ability to mask specific connection managers, variables, and parameters. The configVarsConnsFiltering switch will be ignored if the configType switch is set to None. The default value is True. Users can specify the following values:

 True

  False


Connection Information

Switch

Short Form

Description

/bixExistingConnection:value

/bxec:value

Specifies whether an existing connection manager will be used or if the a new connection manager will be created when applying the Auditing Framework. The default value is False. Users can specify the following values:

  True

  False

/bixConnName:value

/bxcn:value

Specifies the name of the connection manager to be used by the Auditing Framework. If bixExistingConnection switch is set to False and a connection manager already exists with the same name, the existing connection manager will be used instead. If bixExistingConnection switch is set to True, the connection manager name is case-sensitive and must be entered exactly as it appears. The default value is "OLEDB_BIxPress".

/bixDataSource:value

/bxds:value

Specifies the name of the server hosting the BI xPress database (and instance name if applicable). This is the same database that the Monitoring Console will use to display package execution information.

/bixDatabase:value

/bxdb:value

Specifies the name of the database where the Auditing Framework data will be stored. This is the same database that the Monitoring Console will use to display package execution information.

/bixUserName:value

/bxu:value

If provided, the bixUserName switch (alongside the bixPassword switch) sets Integrated Authentication to False and specifies the user name to be used in order to connect to the BI xPress database.

/bixPassword:value

/bxp:value

If provided, the bixPassword switch (alongside the bixUserName switch) sets Integrated Authentication to False and specifies the user name to be used in order to connect to the BI xPress database.


Custom Variable Logging

Switch

Short Form

Description

/logCustVars:value

/lcv:value

Enables custom logging of specific variables. The default value is False. Users can specify the following values:

 True

  False

/custVarDate1:value

/cvdt1:value

Specifies the name of the first variable with a date data type to log during package execution. The custVarDate1 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyDateVariable1".

/custVarDate2:value

/cvdt2:value

Specifies the name of the second variable with a date data type to log during package execution. The custVarDate2 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyDateVariable2".

/custVarDate3:value

/cvdt3:value

Specifies the name of the third variable with a date data type to log during package execution. The custVarDate3 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyDateVariable3".

/custVarNum1:value

/cvn1:value

Specifies the name of the first variable with a numeric data type to log during package execution. The custVarNum1 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyNumericVariable1".

/custVarNum2:value

/cvn2:value

Specifies the name of the second variable with a numeric data type to log during package execution. The custVarNum2 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyNumericVariable2".

/custVarNum3:value

/cvn3:value

Specifies the name of the third variable with a numeric data type to log during package execution. The custVarNum3 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyNumericVariable3".

/custVarString1:value

/cvs1:value

Specifies the name of the first variable with a string data type to log during package execution. The custVarString1 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyTextVariable1".

/custVarString2:value

/cvs2:value

Specifies the name of the second variable with a string data type to log during package execution. The custVarString2 switch will be ignored if the logCustVars switch is set to False. The value may include the variable scope, for example "User::MyTextVariable2".

/custVarString3:value

/cvs3:value

Specifies the name of the third variable with a string data type to log during package execution. The custVarString3 switch will be ignored if the logCustVars switch is set to False. The default value is True. The value may include the variable scope, for example "User::MyTextVariable3".

/custVarPackageScope:value

/cvps:value

Enables logging of the specified variable(s) at the package level during package execution. The custVarPackageScope switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/custVarTaskScope:value

/cvts:value

Enables logging of the specified variable(s) at the task level during package execution. The custVarTaskScope switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/custVarOnError:value

/cvoe:value

Enables logging of the specified variable(s) when the OnError event handler executes. The custVarOnError switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/custVarOnWarning:value

/cvow:value

Enables logging of the specified variable(s) when the OnWarning event handler executes. The custVarOnWarning switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/custVarOnPreExecute:value

/cvope:

value

Enables logging of the specified variable(s) when the OnPreExecute event handler executes. The custVarOnPreExecute switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:

 True

  False

/custVarOnPostExecute:value

/cvops:value

Enables logging of the specified variable(s) when the OnPostExecute event handler executes. The custVarOnPostExecute switch will be ignored if the logCustVars switch is set to False. The default value is True. Users can specify the following values:

 True

  False


Examples

The following example uses a response file that contains all of the necessary switches to apply the auditing framework.



AuditingFramework.CommandLine.exe @"C:\SSIS\AfResponseFile.txt"



The following example adds the Auditing Framework to two SSIS packages located on the File System and uses the BI xPress database named "ProdBIxPress" located on "ProductionServer". In addition, the logging of warnings is turned off.



AuditingFramework.CommandLine.exe /add "C:\SSIS\MasterPackage.dtsx" "C:\SSIS\Staging.dtsx" /location:FileSystem /bixDataSource:"ProductionServer" /bixDatabase:"ProdBIxPress" /logWarnings:False



The following example adds the Auditing Framework to one SSIS package located on the File System and uses the BI xPress database named "ProdBIxPress" located on "ProductionServer". In addition, the custom variable "User::MyDateVar1" will be included in the log, however, logging of the custom variable during the OnPreExecute event has been disabled.



AuditingFramework.CommandLine.exe /a "C:\SSIS\MasterPackage.dtsx" /l:FileSystem /bxds:"ProductionServer" /bxdb:"ProdBIxPress" /lcv:True /cvdt1:"User::MyDateVar1" /cvope:False



The following example removes the Auditing Framework from two SSIS 2012 packages located on the SQL Server  named "ProductionServer".



AuditingFramework.CommandLine.exe /remove "\SSIS\MasterPackage.dtsx" "\MSDB\SSIS\Staging" /location:SqlServer /locationVersion:Sql2012 /dataSource:"ProductionServer"