0

I've got a SQL Job set up to run a SSIS package. The SSIS package has a number of parameters. I want to see what the parameters look like when the job is sending the parameter list.

I have an issue where the package works in dev, but not in prod, as it says it has a mismatched quote. I have a few parameters in a row that have a unc path. Once I remove the trailing backslash, it works, but I want to see what it's sending.

Is there a way to log or see how the parameter list is built from a job running a SSIS package?

Thank you.

JM1
  • 1,595
  • 5
  • 19
  • 41
  • 1
    You could use Profiler\XEvents if you're connecting to SQL Server: https://stackoverflow.com/questions/1952830/how-do-i-get-parameter-values-for-sql-server-query-in-sql-server-profiler – David Browne - Microsoft Jan 26 '23 at 20:40

2 Answers2

1

Assuming you are using SSIS Catalog.

If you navigate to the SSIS project inside the Integration Service Catalog, and run the report Standard Report->All Executions. You can check the last execution run and inside that report will list all the parameters.

Or check the SSISDB tables.

SELECT TOP 100 *
FROM SSISDB.internal.execution_parameter_values
WHERE execution_id = <execution_id>
Simon P
  • 316
  • 1
  • 6
  • Thanks @Simon P! This does give the parameter values. It doesn't show if the values are concatenated together (assuming they are concatenated together). For example, if \\unc\FilePath1\ is a parameter and \\unc\FilePath2\ is the next parameter, does sql concatenate them together like this '\\unc\FilePath1\\\unc\FilePath2\' when the job is executed? – JM1 Feb 06 '23 at 15:01
  • SSIS would treat them as separate parameter values. Are you concatenating inside the SSIS package? – Simon P Feb 06 '23 at 15:13
  • 1
    if you script the SQLAgent job as create, check the @command. `/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1` – Simon P Feb 06 '23 at 15:31
  • Thanks! No, I am not concatenating in the package. Thanks for all of your help, I'll need to check this out. – JM1 Feb 06 '23 at 16:15
  • Thanks @Simon P, your help started me on the path to figure out what the package was doing! – JM1 Feb 09 '23 at 16:02
0

When running the following query, it showed me SQL was sending both, prod and dev parameters, so it was not using all the parameters set in the job.

It was using some parameters from the Catalog > Configure section mixed with ones from the job.

I ended up having to ensure the parameters were set at the Catalog level: right click the project > Configure. (Typically the packages is saved with all dev parameters, then changed on the prod server.)

Once I had the parameters set at the config level, as well as the in the job, everything worked. However, I think there is a bug issue with the software. I can't tell if the parameter is coming from the Sql job, or the catalog configure area. I do have a case where I know at least one is coming from a job area.

In case it's helpful, just put in a partial project name and this will show you the parameters for the latest execution of the project you are working on. Most of this code is not original to me, I just adapted it.

USE SSISDB
    GO    
    DECLARE @SSISprgName VARCHAR(100) = 'ENTER PARTICAL PROJECT NAME HERE'           

 IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

 SELECT TOP 1 execution_id, process_id,start_time,   * 
    FROM catalog.executions AS e --Where end_time is null
    WHERE project_name LIKE '%'+ @SSISprgName +'%'
    ORDER BY  e.start_time DESC
    
    IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
    SELECT TOP 1 e.execution_id 
    INTO #t1
    FROM catalog.executions AS e --Where end_time is null
    WHERE project_name LIKE '%'+ @SSISprgName +'%'
    ORDER BY  e.start_time DESC
    
    SELECT TOP 100 *
    FROM SSISDB.internal.execution_parameter_values
    WHERE execution_id IN (SELECT  execution_id FROM #t1)
JM1
  • 1,595
  • 5
  • 19
  • 41