Configuring and Creating An AlwaysOn Availability Group in SQL Server 2012

In the previous article on AlwaysOn Availability Groups in SQL Server 2012, we looked at The Environmental Setup for An AlwaysOn Availability Group. In this article we turn our attention to the creation and configuration of the Availability Group.

There are two primary steps to creating an AlwaysOn availability group which has to be done only after successful creation of the Windows failover cluster as discussed in the previous article. Firstly enable AlwaysOn on each instance and then create the AlwaysOn Availability Group.

Enabling AlwaysOn High Availability on each SQL Server Instance

First of all you will need to enable AlwaysOn each participating SQL Server instance of the cluster or availability group (by default AlwaysOn is not enabled). Open SQL Server Configuration Manager (Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools) as shown below:

In SQL Server Configuration Manager, select and right-click on the SQL Server service (select the appropriate instance service) and click Properties. On the Properties page, select AlwaysOn High Availability tab and enable AlwaysOn as shown below; notice that the name of the Windows failover cluster in which this instance participates will automatically appear :

After enabling the AlwaysOn High Availability at instance/service level, you will need to restart the service for changes to take effect. Once enabled, the IsHadrEnabled property of the instance will be set to 1. Please note, if there is any changes in the Windows failover cluster that you created, you must disable and enable AlwaysOn High Availability feature once more for the changes to take effect. Also, ensure that the TCP/IP net library/protocol is enabled on the instance as only the TCP/IP protocol is supported by the availability group listener (I will discuss more about availability group listener later in this series).

Creating an AlwaysOn Availability Group

Now to create an availability group. Connect to the primary instance/replica in SSMS (SQL Server Management Studio) and then right-click on the Availability Groups node under the AlwaysOn High Availability node and click the New Availability Group Wizard as shown below:

In the New Availability Group Wizard, skip the introduction page and on the second page, specify a unique name for the availability group that you want to create, in this case I have used AlwaysOn-Demo-AG for the availability group:

On the Select Databases page of the New Availability Group Wizard, select all the databases that you wish to be part of the availability group (all databases in this availability group will failover together as group or unit). As noted before, as with database mirroring you are not required to have only one database here in case of an availability group. Remeber also that a database can be part of one and only one availability group at any time:

Please note in the above image, you can only include databases which meet certain prerequisites for including them in the availability group, for example that database should be in full recovery mode and you should have taken at least one full or differential backup of the database. If you click on the link for the database which does not meet prerequisite, a pop as shown below will appear with details:

On the Select Replicas page, under the Replicas tab of the New Availability Group Wizard, notice that the current instance name has already been included as the primary replica (read-write databases) and next you need to add secondary replicas (you need to have at least one or up to four secondary replicas). Here, can also specify the automatic failover setting/instance or synchronous commit mode. If you set a secondary replica for automatic failover, synchronous commit mode is required and will be set automatically. Next, can specify the readability setting for the secondary replica:

On the Select Replicas page under Endpoint tab, by default endpoints (database mirroring endpoints to connect to each other servers/instances) will be created for all the servers participating in the availability group. You can customize this for specific requirements or leave the default values as is:

On the Select Replicas page (under Backup preferences tab) of the New Availability Group Wizard, you need to specify the preferred location of automated backup for the databases participating in the availability group:

• · Prefer Secondary – Takes an automatic backup on the secondary replica if one is available alterantively it takes a backup on the primary replica
• · Secondary Only – Takes a backup of the database belonging to this availability group which should occur on current secondary replica only. Selecting this means you are basically offloading the backup operation from the primary to the secondary replica.
• · Primary – Backups should always be taken on the current/active primary replica irrespective of the number of secondary replicas available.
• · Any Replica – Backup of database belonging to the availability group can happen on any replica as per the backup priority specified. You can also exclude one or more replicas from the backup operation:

On the Select Replicas page and under Listener tab of the New Availability Group Wizard, you need to specify whether you want to create an availability group listener with the availability group creation itself or whether you wish to create it later on. In either case, note that you can have only one availability group listener for an availability group.

So what is an availability group listener is and what does it do?
An availability group listener provides a connection point to connect to an availability group from client applications. An availability group listener directs incoming connections/requests to the current primary replica of the availability group for read-write operations or to a readable secondary replica for read-only operation. Therefore, the client does not need to know the physical name of the instance it intends to connect to, but instead it will be using the availability group listener and the availability group listener will route requests to the appropriate replica/instance.

When creating availability group listener you need to provide the below information:

· Listener DNS Name or Virtual Network Name (VNN) : This name should be unique across the domain.

· Port – The port number on which the availability group listener will listen for incoming requests. You can use the default port 1433 of SQL Server in which case the client would not be required to provide the port number when connecting. Otherwise the client will need to provide the port number as part of connection string. This port number should be defined appropriately in the firewall to allow for connections.

· Network mode – You can use either DHCP (Dynamic Host Configuration Protocol) or static IP for the availability group listener:

Here I have used DHCP but you need to use static IP addresses for the availability group listener if the availability group is spread across subnets in a multi-subnet domain. In addition, DHCP is not recommended for use in production for an availability group listener as it requires additional time to re-register if the DHCP lease expires.

On the Select Data Synchronization page, specify how you are going to synchronize your secondary replicas’ databases with the primary replica. In this case, because the database is not that large, I have chosen the first option to take a backup from the primary replica and restore them at all on the secondary replicas and for that I provided a shared location for storing the backup files which is accessible from both the primary and secondary replicas.

Alternatively you may prefer to peform the data synchronization manually or only synchronize the data if the databases are already restored on the secondary replicas, so in effect you have three data synchronization options to choose from as shown below:

On the Validation page the wizard runs the validation process to ensure that the given configuration details for the availability group can be created. In this case I chose to restore the database on the secondary replica and hence it also validates if the location for the data/log files exist on secondary replicas. If the validation fails, you can access the details by clicking the link in the Result column. Once you are finished with the validation, click Next:

On Summary page you can review your all selections and information you provided for creating and configuring the availability group. You can go back make any necerssary changes or click Finish to start creating the availability group:

Please note, using New Availability Group Wizard is not the only way to create an availability group, you can automate the process of creating an availability group by using PowerShell cmdlets or T-SQL commands. To generate T-SQL scripts for our selection or configuration information that you provided during this wizard, you can click on Script button to generate T-SQL script commands as shown in the above image for later use.

On Result page, as shown below, you can see the progress and the final status of the availability group creation process. The wizard performs a step-by-step operation for creating availability group, joining secondary replicas, taking back from the primary replica and restoring it to secondary replicas for data synchronization etc.

In the next article in the series we will examine how to connect to an Availability Group as well as maintaining and monitoring an Availability Group.

Source:

http://www.sql-server-performance.com/2012/configuring-creating-alwayson-availability-group-sql-server-2012/

Exploring SSIS Architecture and Execution History Through Scripting

My previous article on SSIS was focused on the architecture and the functioning of the product. I had also provided a few essential T-SQL scripts which provide certain ways for documenting the SSIS environment.

In this article I will focus more on the T-SQL scripting and the ways to reveal configuration, performance and architectural information through scripting.

Exploring SSIS’s Metadata Objects

SSIS Metadata Objects in sys.objects

Let’s start simple by exploring the metadata objects that are related to the SSIS installation.

If we look at the Integration Services metadata objects in SQL 2005 we will notice that the objects contain the ‘DTS’ phrase in their names. By executing the following script in SQL 2005 we will get all objects related to the SSIS metadata (notice that the script is executed in the MSDB context):

USE msdb ;SELECT  *FROM    sys.objectsWHERE   name LIKE '%dts%'

Later on, in the SQL 2008 and later we have objects containing the phrase ‘DTS’ as well as ‘SSIS’ in the names. Execute the following script to view the objects (again, in the context of the MSDB database):

USE msdb ;SELECT  *FROM    sys.objectsWHERE   name LIKE '%dts%'
        OR name LIKE '%ssis%'

Why is this? In SQL Server 2005 you will find dbo.sysdtspackages and dbo. sysdtspackages90, which help SQL Server distinguish between Integration Services packages created in BIDS and legacy packages inherited and transferred from the old SQL Server 2000 DTS (Data Transformation Services).

In SQL Server 2008 and up we find dbo. sysdtspackages and dbo. sysssispackages, where the first table contains legacies, and the second – the BIDS packages with versions from 2005 and 2008.

SSIS Metadata Objects in other system tables

In SQL 2008 and up we have:

name - 2008
sysdtscategories	One row for each category description
sysdtspackagelog	Legacy
sysdtspackages	Legacy
sysdtssteplog	Legacy
sysdtstasklog	Legacy
sysssislog	One row per entry generated by SSIS package at runtime (when the SQL Server log provider is used)
sysssispackagefolders	One row for each folder in the SSIS structure
sysssispackages	One row for each SSIS package

… and in SQL 2005 there is …

name - 2005
sysdtscategories	One row for each category description
sysdtslog90	One row per entry generated by SSIS package at runtime
sysdtspackagefolders90	One row for each folder in the SSIS structure
sysdtspackagelog	Legacy
sysdtspackages	Legacy
sysdtspackages90	One row for each SSIS package
sysdtssteplog	Legacy
sysdtstasklog	Legacy

Structure and contents of the SSIS packages

As we know, the SSIS packages are just structured XML files that contain all information needed for the package to carry out its tasks. In other words, the SSIS package itself contains the objects in the flows, the precedence, the connections and their configurations.

SSIS Packages may be saved on the file system, or in MSDB repository. In the case of the package being saved in MSDB, the package definition is saved in the packagedata column of the dbo.sysssispackages table (or in dbo.sysdtspackages90 in SQL Server 2005).

The column itself is of the image datatype, hence in order for us to retrieve the contents, we need to cast it as a VARBINARY(MAX) first, and then as a XML data type.

Depending on the security level of the package, however, it might not be very easy to explore the contents of the package definitions in MSDB; in case the package is encrypted, the package definition will begin with theEncryptedData tag.

Retrieving the definitions of the SSIS Packages

So, here is how to retrieve the definitions of the SSIS packages in MSDB:

In 2005:

SELECT     p.[name] AS [PackageName]
           ,[description] AS [PackageDescription]
           ,CASE [packagetype]
               WHEN 0 THEN 'Undefined'
               WHEN 1 THEN 'SQL Server Import and Export Wizard'
               WHEN 2 THEN 'DTS Designer in SQL Server 2000'
               WHEN 3 THEN 'SQL Server Replication'
               WHEN 5 THEN 'SSIS Designer'
               WHEN 6 THEN 'Maintenance Plan Designer or Wizard'
           END     AS [PackageType]
           ,CASE [packageformat]
               WHEN 0 THEN 'SSIS 2005 version'
               WHEN 1 THEN 'SSIS 2008 version'
           END AS [PackageFormat]
           ,p.[createdate]
           ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysdtspackages90]     p

In 2008 and up:

SELECT     p.[name] AS [PackageName]
           ,[description] AS [PackageDescription]
           ,CASE [packagetype]
               WHEN 0 THEN 'Undefined'
               WHEN 1 THEN 'SQL Server Import and Export Wizard'
               WHEN 2 THEN 'DTS Designer in SQL Server 2000'
               WHEN 3 THEN 'SQL Server Replication'
               WHEN 5 THEN 'SSIS Designer'
               WHEN 6 THEN 'Maintenance Plan Designer or Wizard'
           END     AS [PackageType]
           ,CASE [packageformat]
               WHEN 0 THEN 'SSIS 2005 version'
               WHEN 1 THEN 'SSIS 2008 version'
           END AS [PackageFormat]
           ,p.[createdate]
           ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysssispackages]     p

Now that we have the definition, what can we do with it? We can parse it and extract some useful data.

Extracting connection strings from an SSIS definition

Here is how to retrieve the data connection strings:

In SQL 2005:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]',
    'varchar(100)') AS DelayValidation,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ObjectName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]',
    'varchar(100)') AS Description,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)') Retain,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionStringFROM  --( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysdtspackages90]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML (SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysdtspackages90] c ON PackageXML.id = c.id

In SQL 2008 and up:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]',
    'varchar(100)') AS DelayValidation,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ObjectName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]',
    'varchar(100)') AS Description,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)') Retain,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionStringFROM  --( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysssispackages]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML (SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id

Extracting connection strings from an SSIS definition

Here is how to retrieve the package configurations:

In SQL 2005:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS ConfigurationType,
  CASE CAST(SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS INT)
    WHEN 0 THEN 'Parent Package'
    WHEN 1 THEN 'XML File'
    WHEN 2 THEN 'Environmental Variable'
    WHEN 3 THEN 'Registry Entry'
    WHEN 4 THEN 'Parent Package via Environmental Variable'
    WHEN 5 THEN 'XML File via Environmental Variable'
    WHEN 6 THEN 'Registry Entry via Environmental Variable'
    WHEN 7 THEN 'SQL Server'
  END AS ConfigurationTypeDesc,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationVariable"][1]',
    'varchar(100)') AS ConfigurationVariable,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ConfigurationName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationString"][1]',
    'varchar(100)') AS ConfigurationStringFROM  ( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysdtspackages90]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:Configuration') SSIS_XML (SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysdtspackages90] c ON PackageXML.id = c.id

In SQL 2008 and up:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS ConfigurationType,
  CASE CAST(SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS INT)
    WHEN 0 THEN 'Parent Package'
    WHEN 1 THEN 'XML File'
    WHEN 2 THEN 'Environmental Variable'
    WHEN 3 THEN 'Registry Entry'
    WHEN 4 THEN 'Parent Package via Environmental Variable'
    WHEN 5 THEN 'XML File via Environmental Variable'
    WHEN 6 THEN 'Registry Entry via Environmental Variable'
    WHEN 7 THEN 'SQL Server'
  END AS ConfigurationTypeDesc,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationVariable"][1]',
    'varchar(100)') AS ConfigurationVariable,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ConfigurationName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationString"][1]',
    'varchar(100)') AS ConfigurationStringFROM  ( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysssispackages]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:Configuration') SSIS_XML (SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id

There are many other aspects to be explored in the definitions of the SSIS packages, and it is all matter of finding the node names and parsing them.

In the remaining part of this article, I would like to shift our attention to these areas: the interaction between SQL Agent and the SSIS packages and some scripts to gather performance statistics.

Overriding the package internal configurations

The SSIS packages can be executed in several ways: as a scheduled job from the SQL Server Agent or from the command line (or even from a batch file).

Regardless of which method is used for the execution, it is always the DTExec.exe who carries the task.

Before executing the SSIS package, the SQL Server Agent or the command line script have to form an execution string and pass parameters to the DTExec, and thus control the execution of the package.

Here is a script which shows all SQL Agent jobs steps which execute SSIS packages and the custom configurations provided through the SQL Agent job:

USE [msdb]
GOSELECT j.job_id,
       s.srvname,
       j.name,
       js.subsystem,
       js.step_id,
       js.command,
       j.enabled,
       js.output_file_name,
       js.last_run_outcome,
       js.last_run_duration,
       js.last_run_retries,
       js.last_run_date,
       js.last_run_time,
        js.proxy_id FROM   dbo.sysjobs jJOIN   dbo.sysjobsteps js
   ON  js.job_id = j.job_id JOIN   MASTER.dbo.sysservers s
   ON  s.srvid = j.originating_server_id--filter only the job steps which are executing SSIS packages WHERE  subsystem = 'SSIS'--use the line below to enter some search criteria
--AND js.command LIKE N'%ENTER_SEARCH%'GO

As you noticed, you can even use the script above to filter and search through the configurations of the SQL Agent Jobs. For example, you can search for all jobs which are executing encrypted SSIS packages by using …

AND    js.command LIKE N'%/DECRYPT%'

…as a search criteria in the above script. You may also want to search for a server name, for example.

Exploring execution history

Finally, let’s look into some execution history of the SSIS packages which are scheduled as SQL Server Agent jobs.

The following script will return all SQL Server Agent Jobs, which are currently (as of the moment of the execution of the script) executing SSIS packages and also the last execution time and duration, as well as the execution command.

SET NOCOUNT ON-- Check if the SQL Server Agent is runningIF EXISTS ( SELECT  1
            FROM    MASTER.dbo.sysprocesses
            WHERE   program_name = N'SQLAgent - Generic Refresher' )
    BEGIN
        SELECT  @@SERVERNAME AS 'InstanceName' ,
                1 AS 'SQLServerAgentRunning'
    END
ELSE
    BEGIN
        SELECT  @@SERVERNAME AS 'InstanceName' ,
                0 AS 'SQLServerAgentRunning'             
        RAISERROR('The SQL Server Agent is not running.', 16, 1) WITH SETERROR;             
    END-- Execute the scriptIF EXISTS ( SELECT  *
            FROM    tempdb.dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]') )
    DROP TABLE [tempdb].[dbo].[Temp1]
GOCREATE TABLE [tempdb].[dbo].[Temp1]
    (
      job_id UNIQUEIDENTIFIER NOT NULL ,
      last_run_date NVARCHAR(20) NOT NULL ,
      last_run_time NVARCHAR(20) NOT NULL ,
      next_run_date NVARCHAR(20) NOT NULL ,
      next_run_time NVARCHAR(20) NOT NULL ,
      next_run_schedule_id INT NOT NULL ,
      requested_to_run INT NOT NULL ,
      request_source INT NOT NULL ,
      request_source_id SYSNAME COLLATE database_default
                                NULL ,
      running INT NOT NULL ,
      current_step INT NOT NULL ,
      current_retry_attempt INT NOT NULL ,
      job_state INT NOT NULL
    )DECLARE @job_owner SYSNAME
DECLARE @is_sysadmin INT
SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)SET @job_owner = SUSER_SNAME()INSERT  INTO [tempdb].[dbo].[Temp1]
        EXECUTE MASTER.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
        UPDATE  [tempdb].[dbo].[Temp1]SET     last_run_time = RIGHT('000000' + last_run_time, 6) ,
        next_run_time = RIGHT('000000' + next_run_time, 6) ;-----SELECT  j.name AS JobName ,
        j.enabled AS Enabled ,
        CASE x.running
          WHEN 1 THEN 'Running'
          ELSE CASE h.run_status
                 WHEN 2 THEN 'Inactive'
                 WHEN 4 THEN 'Inactive'
                 ELSE 'Completed'
               END
        END AS CurrentStatus ,
        COALESCE(x.current_step, 0) AS CurrentStepNbr ,
        CASE x.running
          WHEN 1 THEN js.step_name
          ELSE NULL
        END AS CurrentStepName ,
        CASE WHEN x.last_run_date > 0
             THEN CONVERT (DATETIME, SUBSTRING(x.last_run_date, 1, 4) + '-'
                  + SUBSTRING(x.last_run_date, 5, 2) + '-'
                  + SUBSTRING(x.last_run_date, 7, 2) + ' '
                  + SUBSTRING(x.last_run_time, 1, 2) + ':'
                  + SUBSTRING(x.last_run_time, 3, 2) + ':'
                  + SUBSTRING(x.last_run_time, 5, 2) + '.000', 121)
             ELSE NULL
        END AS LastRunTime ,
        CASE h.run_status
          WHEN 0 THEN 'Fail'
          WHEN 1 THEN 'Success'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancel'
          WHEN 4 THEN 'In progress'
        END AS LastRunOutcome ,
        CASE WHEN h.run_duration > 0
             THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
                  + ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
                                                              / 100 % 100 )
                  * 60 + ( h.run_duration % 100 )
             ELSE NULL
        END AS LastRunDuration ,
        js.command AS SSISPackageExecutionCommandFROM    [tempdb].[dbo].[Temp1] x
        LEFT JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
        JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
        LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
        LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id
                                                    AND x.last_run_date = h.run_date
                                                    AND x.last_run_time = h.run_time
                                                    AND h.step_id = 0WHERE   x.running = 1
        AND js.subsystem = 'SSIS'DROP TABLE [tempdb].[dbo].[Temp1]

In conclusion, SSIS is a vast product which provides significant amount of metadata available to the SQL Server administrator. In this article I have shown the way to explore the SSIS metadata through some scripts and hopefully they will make the daily administration of your SSIS environments much easier.

Source: 

http://www.simple-talk.com/sql/ssis/-exploring-ssis-architecture-and-execution-history-through-scripting/