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

Trouble Shooting Wait Type PREEMPTIVE_OS_WRITEFILEGATHER

In this real world scenario we encounter the SQL Server Wait type PREEMPTIVE_OS_WRITEFILEGATHER.
During production hours we started to receive alerts about our Web Application hanging. Users where unable to login to the Web App, first they would experience a hanging in the web browser and eventually a timeout error would appear.

The first place to check was the Sql Instance. It was noticed some Sql Server processes where being blocked for a period of a few minutes before they proceeded to complete. We could quickly see a bottleneck of processes gathering on the Sql Server.

The next immediate step to take was to see the Wait Types that where occurring on the Sql Server. When reviewing the Wait types the one that stood out was PREEMPTIVE_OS_WRITEFILEGATHER, as the blocking process was showing up this Wait Type.

We could then quickly identify what was cusing the bottleneck of blocking process. The Sql Server was performing a Transaction log file Autogrowth and it was attempting to Zero extra space on the file. The Transaction Log file size was 40GB and it was set to Autogrow by 10%. A quick additional investigation of the Error Log files showed up the following errors.

There are several suggestions how to prevent this from occurring. Such as improved planning of the expected file size growth, configure the SQL Server to perform Instant File Initialization or even wait for the process to complete, which could take sometime. In this real world scenario the quickest method to remedy the situation was to reduce the expected file growth size for the Transaction log.

The Autogrowth was changed from a % to a fixed growth size of 500MB. When this was set the processes immediately resumed back to normal. The users could access the Web App without the timeouts occurring. The production environment went back to normality.