SSIS Checking log progress

If your SSIS package logs its progress in the table sysssislog, you can track its progress with the following query. 

Its assumed SSIS Logging is using OnPostExecute and OnPreExecute entries to identity the start and stop of the SSIS task.  

This sql query is used for an SSIS package that pulls data from source to destination table. 

WITH

Post AS (

SELECT ID, event, source, starttime, endtime, executionid FROM Msdb.dbo.sysssislog WHERE CONVERT( varchar(10), Starttime, 103 ) =CONVERT(varchar(10), GETDATE() , 103 ) AND EVENT='User:OnPostExecute' 

),

Pre AS (

SELECT ID, event, source, starttime, endtime, executionid FROM Msdb.dbo.sysssislog WHERE CONVERT( varchar(10), Starttime, 103 ) =CONVERT(varchar(10), GETDATE() , 103 ) AND EVENT='User:OnPreExecute' 

)

SELECT Pre.ID, Pre.source AS [TABLE], Pre.starttime , Post.EndTime ,DATEDIFF(MI,Pre.starttime , Post.EndTime ) as Minutes, Pre.executionid

FROM PRE

LEFT JOIN POST ON Pre.source = Post.source AND Pre.executionid = Post.executionid

order by Pre.starttime DESC

JQuery Execute An SSIS Package File From Web Page Using C Sharp

The purpose of this project was to allow the execution of SSIS packages files (.dtsx) via a Web Single Page Application.

The code can be accessed at https://github.com/brenmcveigh/SSIS-JQuery

The code is mixture of Jquery for the front end web page and C# to handle the SSIS side. 

Business users without access to SSMS can use the Web page to execute the SSIS packages on demand.

The Web application is built around the SSIS packages it finds in the directory,rendering the web page elements per each package.

When you click on the SSIS package button it will generate a timer and when completed will show either Success or Error.  (Errors will generate a popup message)

Below are SSIS packages that are set intentionally to fail showing an red X icon. It generates a popup message showing the SSIS Package error.

The application looks in the Web.Config file for the designated directory where the SSIS packages reside.

In the above screenshots are sample SSIS packages that reside in the directory 

"C:\SSIS Files" as mentioned in the web.config.

<appSettings>
    <add value="C:\SSIS Files" key="SSISDirectory"/>

An IIS Application pool may run under a Domain account if the SSIS packages has to access restricted file shares or if a Sql proxy account is needed to access locked down objects in a Sql Server (Tables\ Views\ Stored procs etc).