Programming SSIS Package Dataflow Using C# , DB2

If you work in an environment where you maintain large volumes of SSIS packages, this can be a time consuming overhead. 

Especially if your handling 100's of SSIS packages, the need to automate maintenance routines becomes more apparent. 

One of the common work tasks with ETL's is the continual adding of new tables or fields. When your operating in an workplace where this is an daily occurrence you can envisage how this task should be done more efficiently. 

In this program its coded primarily to work with DB2 as source and Sql Server as destination. The SSIS DB2 connections are using an Microsoft® OLEDB Provider for DB2.

Below is the tool user manual and the link to the source code.
The source code is written in C# Forms, .Net Framework 4 and Visual Studio 2010.

The source code is available at https://github.com/brenmcveigh/SSIS-DB2

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).

Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)) (Microsoft Visual Studio)

The following error was generated in Visual Studio for SSIS packages when using the Advanced Editor for Data Flow Tasks.
Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)) (Microsoft Visual Studio)

Several attempts reinstalling Visual studio and Service Packs did not resolve the error.
Also tried registering the library “dteproperties.tlb” using regtlibv12.exe suggested from here did not resolve the issue.

I then used Process Monitor to capture the dll’s Visual Studio was trying to use for the Data flow task Advanced Editor.
A quick look at the results in Process Monitor identified the registry key it was searching for in the registry.
 
After searching for this key in the registry, it was shown to be aligned to DTSPipeline.dll.
The final step was to register this dll  %windir%\syswow64\regsvr32  "%ProgramFiles(x86)%\..\..\..\DTSPipeline.dll" and problem solved.