question

RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 asked ·

Find which computer ran an SSIS package

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.

sql-server-integration-services
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RusselLoski-0634 avatar image
1 Vote"
RusselLoski-0634 answered ·

Thank you @YitzhakKhabinsky-0887 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. @YitzhakKhabinsky-0887 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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

Hi @RusselLoski-0634,

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:

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>

· 5 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The caller_name and caller_sid are helpful.

What is lacking is the host from which the SSIS package was started and the process that is running.

I'm thinking the equivalent of HostName ("The name of the computer on which the client is running. This data column is populated if the host name is provided by the client. To determine the host name, use the HOST_NAME function." https://docs.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace?view=sql-server-ver15) and ApplicationName (same link).

Right now, I don't know whether there is a batch file running on the current server calling dtexec or if perhaps some job on another server is calling the package. With HostName, I'm closer to an answer and ApplicationName is likely to help narrow the possibilities.

0 Votes 0 ·
 <server_name>SSIS0320318</server_name>
 <machine_name>SSIS0320318</machine_name>

Please take a look at these data elements.

What is the value of the operation_type?

By the way, doesn't matter how a SSIS package is launched, when a package is hosted in the SSIS Catalog, it will cause a child process to get spawned from the SQL Service itself. This process is the ISServerExec.exe.

0 Votes 0 ·
RusselLoski-0634 avatar image RusselLoski-0634 YitzhakKhabinsky-0887 ·

My understanding is that this is the current SQL Server that the package is running on not the client host that initiated the request. But I can be wrong.

I will see if I can find an environment where I can test run a package from SQL Agent on another machine to see what results.

0 Votes 0 ·
Show more comments
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @RusselLoski-0634 ,

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.



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.