Create SQL Jobs using TFS project

• river1

  SSC-Insane

  Points: 24300

  More actions

  December 22, 2017 at 1:39 am

  #409889

  Dear all,

  I have a TFS project that generates jobs. Code sample below:

  -- variable declarations

  DECLARE @sqlJobName NVARCHAR(255) ;

  DECLARE @sqlJobSSISPackagePath NVARCHAR(max) ;

  DECLARE @sqlJobSSISParameters NVARCHAR(max) ;

  DECLARE @sqlJobSourceLinkedServer NVARCHAR(max) ;

  DECLARE @sqlJobSourceDatabaseName NVARCHAR(max) ;

  DECLARE @sqlJobSSISServer NVARCHAR(200) ;

  -- common parameters for all jobs

  SET @sqlJobSSISPackagePath = 'dwhSourceToStaging\dwhSourceToStaging\SrcToStg_Master.dtsx' ;

  SET @sqlJobSSISServer = '$(SSISServer)' ;

  -- specific job configuration for ICRM job

  SET @sqlJobName = '00 - DWH Source To Staging - ICRM' ;

  SET @sqlJobSourceLinkedServer = '$(SrcToStgICRMServer)' ;

  SET @sqlJobSourceDatabaseName = '$(SrcToStgICRMGatewayDB)' ;

  SET @sqlJobSSISParameters = '/Par "\"pSourceConnectionName\"";"\"dwhICRMGateway\"" '

  + '/Par "\"pSourceSystem\"";"\"ICRM\"" '

  + '/Par "\"pSourceLinkedServer\"";"\"' + @sqlJobSourceLinkedServer + '\"" '

  + '/Par "\"pSourceDatabase\"";"\"' + @sqlJobSourceDatabaseName + '\"" '

  ;

  -- run the stored procedure to set up or update the ICRM job

  EXEC [ETL].uspCreateOrUpdateSSISJob

  @sqlJobName = @sqlJobName

  , @sqlJobSSISPackagePath = @sqlJobSSISPackagePath

  , @sqlJobSSISParameters = @sqlJobSSISParameters

  , @sqlJobSSISServer = @sqlJobSSISServer

  I would like to changhe the way that those jobs are created in terms of the logging level. Currently they are created with logging level equal to basic. I would like to set it to None. how can I change the code to create this ?

  Probably what I need is just to add another  variable.

  Can you please help?

  Thank you

  P.S  - Addind a little more of verbose to it:

  I have several SSIS packages that are set to use the logging level = none. Those SSIS packages are called by SQL Server Jobs. In those jobs the logging level is set to basic. I would like to change then to none. I know how to do this from the GUI (MS) but I don't know how to do it from a t-sql perspective instead of using the GUI. Can you please help? thnak you very much