DRAG DROP
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project
deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer
than usual. Which three actions should you perform in sequence? (To answer, move the
appropriate actions from the list ofactions to the answer area and arrange them in the correct
order.)
Answer: See the explanation
Explanation:
1. Connect to SSISDB.
2. Query against execution_component_phases and executions.
3. Execute queryRef:
http://msdn.microsoft.com/en-us/library/hh230981.aspx
http://msdn.microsoft.com/en-us/library/ff878089.aspx
I think then point 2 must be “Query against execution_component_phases and executables.” . “An executable is a task or container that you add to the control flow of a package.” – https://msdn.microsoft.com/en-us/library/hh479595.aspx.
Executions is on whole package level, not different tasks in control flow.
0
0
It should be
1. Connect to SSISDB.
2. Execute query
3. Query against execution_component_phases and executions.
You have to execute the package to get the data for query.
1
0
1. Connect to SSISDB.
2. Query against execution_component_phases and executables.
3. Execute query
To find the execution time of each task we query catalog.executables not catalog.executions
0
1
The EXECUTABLES do not contain any START or END TIME.
An executable is a task or container that you add to the control flow of a package.
Column name Data type Description
executable_id bigint The unique identifier for the executable.
execution_id bigint The unique identifier for the instance of execution.
executable_name nvarchar(4000) The name of the executable.
executable_guid nvarchar(38) The GUID of the executable.
package_name nvarchar(260) The name of the package.
package_path nvarchar(max) The path of the package.
IT DOES EXECUTIONS
Displays the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package.
Column name Data type Description
execution_id bigint The unique identifier (ID) for the instance of execution.
folder_name sysname(nvarchar(128)) The name of the folder that contains the project.
project_name sysname(nvarchar(128)) The name of the project.
package_name nvarchar(260) The name of the first package that was started during execution.
reference_id bigint The environment that is referenced by the instance of execution.
reference_type char(1) Indicates whether the environment can be located in the same folder as the project (relative reference) or in a different folder (absolute reference). When the value is R, the environment is located by using a relative reference. When the value is A, the environment is located by using an absolute reference.
environment_folder_name nvarchar(128) The name of the folder that contains the environment.
environment_name nvarchar(128) The name of the environment that was referenced during execution.
project_lsn bigint The version of the project that corresponds with the instance of execution. This number is not guaranteed to be sequential.
executed_as_sid varbinary(85) The SID of the user who started the instance of execution.
executed_as_name nvarchar(128) The name of the database principal that was used to start the instance of execution.
use32bitruntime bit Indicates if the 32-bit runtime is used to run the package on a 64-bit operating system. When the value is 1, the execution is performed with the 32-bit runtime. When the value is 0, the execution is performed with the 64-bit runtime.
object_type smallint The type of object. The object may be a project (20) or a package (30).
object_id bigint The ID of the object affected by the operation.
status int The status of the operation. The possible values are created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).
start_time datetimeoffset The time when the instance of execution was started.
end_time datetimeoffsset The time when the instance of execution ended.
caller_sid varbinary(85) The security ID (SID) of the user if Windows Authentication was used to log on.
caller_name nvarchar(128) The name of the account that performed the operation.
process_id int The process ID of the external process, if applicable.
stopped_by_sid varbinary(85) The security ID (SID) of the user who stopped the instance of execution.
stopped_by_name nvarchar(128) The name of the user who stopped the instance of execution.
total_physical_memory_kb bigint The total physical memory (in megabytes) on the server when the execution is started.
available_physical_memory_kb bigint The available physical memory (in megabytes) on the server when the execution is started.
total_page_file_kb bigint The total page memory (in megabytes) on the server when the execution is started.
available_page_file_kb bigint The available page memory (in megabytes) on the server when the execution is started.
cpu_count int The number of logical CPUs on the server when the execution is started.
server_name nvarchar(128) The Windows server and instance information for a specified instance of SQL Server.
machine_name nvarchar(128) The computer name on which the server instance is running.
dump_id uniqueidentifier The ID of an execution dump.
0
0