Passing paramaeter in a job

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  October 20, 2008 at 11:57 am

  #214265

  Hi all

  I want to know how can i pass a parameter in job and use that in my packages in the steps...

  have anyone done sumthing like this before?

  Thanks

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Ol'SureHand

  SSCrazy

  Points: 2744

  More actions

  October 20, 2008 at 5:14 pm

  #887367

  My (working) solution goes like this - a bit convoluted and scary security-wise,

  but I have not been able to find a better way yet.

  That is because - to run a package - the SQL user needs privileges to run

  xp_cmdshell as well as Integration Services - just a hair-breadth short of SA!

  My application "impersonates" a less privileged user (as dbo) therefore needs

  these contortions to start the SSIS package under different credentials.

  However, parameter passing is clearly shown in the command line prepared below.

  Enjoy

  1. An Application calles PT_sp_ImportData with a single parameter

  (that is what is expected in the SSIS package as strLocationID)

  CREATE PROCEDURE [dbo].[PT_sp_ImportData]

  (

  @i_varLocation varchar(20) = NULL

  ) AS

  begin -- proc

  declare @intErrorNum int

  if (@i_varLocation is null)

  begin

  select @i_varLocation = '1'

  end

  set @strCMDSSIS = ' /set \Package.Variables[User::strLocationID].Properties[Value];' + @i_varLocation

  exec @intErrorNum = dbo.PT_xdts_StartPackage '\mySSISpackage'

  ,'SQLSrv_instance'

  ,'connManager_In_SSISpkg'

  ,'myDatabaseName'

  , @strCMDSSIS

  end -- proc

  2. Called Stored Proc : another SP which kick-starts the package (running as sa..)

  (despite the xdts, it's a mere user proc...)

  CREATE PROCEDURE [dbo].[PT_xdts_StartPackage]

  (

  @i_strPackageName as varchar(200)

  -- NOTE: package name must be preceded by \ as running SQL Store (not MSDB file).

  -- If containing spaces, must be enclosed in double quotation marks eg "\ExportToMarquee"

  ,@i_strServerName as varchar (20)

  ,@i_strconnMgrName as varchar (60)

  ,@i_strCatalogName as varchar(60)

  ,@i_strParam1 as varchar(200) = NULL

  )

  AS

  BEGIN -- proc

  declare @cmd varchar(4000)

  set @cmd = 'dtexec /SQL ' + @i_strPackageName + ' /SERVER ' + @i_strServerName + ' /CONNECTION ' + @i_strconnMgrName +';"Data Source=' + @i_strServerName + ';User ID=sa;Password=THisisClearTextPwd;Initial Catalog='

  set @cmd = @cmd + @i_strCatalogName + ';Provider=SQLNCLI.1;Persist Security Info=True;" '

  set @cmd = @cmd + ' /USER sa /PASSWORD TheClearTextPasswordAgain /CHECKPOINTING OFF /REPORTING V '

  if @i_strParam1 IS NOT NULL

  set @cmd = @cmd + @i_strParam1

  exec master.dbo.xp_cmdshell @cmd

  END -- proc

  /*