Find which computer ran an SSIS package

Question

A package is being run on our SQL Server (SSIS 2017. Package is in Catalog deployment) twice around the same time. One time is tied to a SQL Agent job. I cannot figure out what is launch the other execution. There are no other jobs that launch this package on this server. I need more information about how I might find out where this package is being launched from. If I could get the host that launches the package, I would be much further along. Questions: 1) Is there a place with more detailed information about the execution? I know the "run as" user. But I don't know the host name. And I don't know the application that ran this (this would be helpful, though not required). 2) Would a standard SQL trace on the SSISDB capture the information I need? If dtexec is used, does it leave a trace in SQL trace (or extended events)? I get push back for any trace that I run, so I need to be certain that it is likely to capture what we need.

Answer 1

Thank you @Yitzhak Khabinsky and @Monalv-MSFT for taking the time to respond to my question.

Before I answer my own question, let me restate the problem I had:

I need to know what process started the SSIS Package running. If SQL Agent on Server2 launched Package1 on Server1, I needed some log that indicated the process was started on Server2. It would also be helpful to know that it was SQL Agent that launched the Package1 and not say DTEXEC running on Server1 or even launching from SSMS.

Both solutions pointed to the SSIS catalog, which is where I had hoped to find the answer to my question. @Yitzhak Khabinsky pointed to the catalog.executions view, which returns MachineName and ServerName. The problem is that that value always relates to the server on which SSIS is running, thus no matter where I start running the package from, it always returns Server1, the server that runs the package and not Server2, the server from which the package was run. @Monalv-MSFT suggested extending logging, by changing the logging level to Verbose. First of all, I still found no evidence of the calling server (Server2) in the catalog.executions view. One other issue with @Monalv-MSFT 's answer: I would need to know where the package was being launched so that I could set this parameter. I definitely don't want to set the LoggingLevel to Verbose by default.

So, I was able to get permission to run an experiment where I ran a simple package while using SQL Profiler trace. I ran the package 6 times (3 times each from 2 different servers). I ran the package from SQL Agent, using dtexec in the command line and by using SSMS.

I found that there were at least two client processes that were spun up as a result of using SQL Agent and dtexec. One had the hostname of the computer I started running the package from (Server2). This client process called various procedures in the catalog schema to start the SSIS package running (create_execution, set_execution_parameter_values, start_execution) and at the end there is a query to get the status of the execution. The second client process runs with the SSIS server (Server1) as the hostname. This process gets the information set by the client using procedures in the internal schema and it logs the progress.

From this, I found that if I look for the create_execution, I can identify the HostName as the computer that ran the package.

There was some good information in the SSIS catalog. One of the parameters that is set using set_execution_parameter is the "CALLER_INFO". SQL Agent sets this value to SQLAGENT and dtexec sets this value to empty string. You can view this information using the catalog view "execution_parameter_values".

So using SQL Trace (and I'm certain extend events) you can identify the computer that starts the SSIS package and using execution_parameter_values, you can determine what is calling this.

I left out SSMS. Every line in the SQL Profiler trace was local to the SSIS server (Server1). Interestingly, there was no call to create_execution, start_execution and not surprisingly, there was no call to get information about the status at the end. The package just started running.

Thank you again for your help.

Answer 2

Hi @Russel Loski ,

SSISDB has a handy SSISDB.catalog.executions table that logs every single execution of SSIS packages.

There are columns of interest for your question:

• package_name
• executed_as_name
• operation_type
• status
• caller_name
• etc.

It is documented here:

• catalog-executions-ssisdb-database
• catalog-operations-ssisdb-database

SQL (I added XML output just for better visibilty.)

select top (1) *  
from SSISDB.catalog.executions  
for XML PATH('r'), TYPE, ROOT('root');  

Output

<root>  
  <r>  
    <execution_id>34</execution_id>  
    <folder_name>LAPD</folder_name>  
    <project_name>NIBIRU</project_name>  
    <package_name>OracleConnection.dtsx</package_name>  
    <project_lsn>1</project_lsn>  
    <executed_as_sid>AQUAAAAAAAUVAAAAfd1eySupT9clXWE26w8BAA==</executed_as_sid>  
    <executed_as_name>LAPD\DINO300154</executed_as_name>  
    <use32bitruntime>0</use32bitruntime>  
    <operation_type>200</operation_type>  
    <created_time>2020-09-17T08:11:52.2317320-04:00</created_time>  
    <object_type>20</object_type>  
    <object_id>1</object_id>  
    <status>7</status>  
    <start_time>2020-09-17T08:11:52.8723767-04:00</start_time>  
    <end_time>2020-09-17T08:11:59.4126068-04:00</end_time>  
    <caller_sid>AQUAAAAAAAUVAAAAfd1eySupT9clXWE26w8BAA==</caller_sid>  
    <caller_name>LAPD\DINO300154</caller_name>  
    <process_id>9376</process_id>  
    <dump_id>C260575C-E1BF-484D-A80C-8D38A7F9B221</dump_id>  
    <server_name>SSIS0320318</server_name>  
    <machine_name>SSIS0320318</machine_name>  
    <total_physical_memory_kb>16776140</total_physical_memory_kb>  
    <available_physical_memory_kb>7784260</available_physical_memory_kb>  
    <total_page_file_kb>19266508</total_page_file_kb>  
    <available_page_file_kb>12443532</available_page_file_kb>  
    <cpu_count>2</cpu_count>  
    <executed_count>1</executed_count>  
  </r>  
</root>  

Answer 3

Hi @Russel Loski ,

Please select a logging level as Verbose in SQL Agent Job.

Select a logging level
The following built-in logging levels are available. You can also select an existing customized logging level. This topic contains a description of customized logging levels.
Verbose
All events are logged, including custom and diagnostic events.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.