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.

PowerShell IIS Web Farm Administration

If you work in an environment where your responsible for an IIS Web farm, your role can revolve around dozens of administrative tasks. If your dealing with 10's of web servers in your web farm it can be time consuming to manually maintain and support your servers. Such as configuring application pools, changing web.config settings or creating virtual directories etc.

With the advent of Powershell this has made the tasks faster and more accurate when implementing web server changes.

Below is a series of Powershell scripts I written to automate the tasks of maintaining web servers.

Backup a File on All Web Servers

#Backup a File on All Web Servers 

$Source = "C:\inetpub\xyz.asp"
$BackupFile = "C:\inetpub\zxc.asp-BACKUP"

$servers='MyWebServer01','MyWebServer02','MyWebServer03','MyWebServer04','MyWebServer05'

$Username  = read-Host  "Enter your Windows Login: "
$Password  = read-Host -AsSecureString   "Windows Server Password:"
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass

ForEach ($server in $servers)
{
       invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 

       {
          import-module -Name webadministration
          Copy-Item -Path $Source  -Destination  $BackupFile                             
       }
}

Backup All Web Config Files
# Backs up all the Web config files that have a connection string on the Web Server

$BackupFile = "-BACKUP"
$Files =   

'C:\inetpub\MyWebSite\MyFolder01\Web.Config'
' C:\inetpub\MyWebSite\MyFolder02\Web.Config'
' C:\inetpub\MyWebSite\MyFolder03\Web.config',
' C:\inetpub\MyWebSite\MyFolder04\Web.config',
' C:\inetpub\MyWebSite\MyFolder05\Web.config',
' C:\inetpub\MyWebSite\MyFolder06\Web.config',
' C:\inetpub\MyWebSite\MyFolder07\Web.config',
' C:\inetpub\MyWebSite\MyFolder08\Web.config'
' C:\inetpub\MyWebSite\MyFolder09\Web.config' 

ForEach ($server in $servers)
{   
  Write-host $Server$BackupFile  
  Copy-Item -Path $server  -Destination  $Server$BackupFile        
}




Change Part of Connection Strings In Web Config Files
$Files =   

'C:\inetpub\MyWebSite\MyFolder01\Web.Config'
' C:\inetpub\MyWebSite\MyFolder02\Web.Config'
' C:\inetpub\MyWebSite\MyFolder03\Web.config',
' C:\inetpub\MyWebSite\MyFolder04\Web.config',
' C:\inetpub\MyWebSite\MyFolder05\Web.config',
' C:\inetpub\MyWebSite\MyFolder06\Web.config',
' C:\inetpub\MyWebSite\MyFolder07\Web.config',
' C:\inetpub\MyWebSite\MyFolder08\Web.config'
' C:\inetpub\MyWebSite\MyFolder09\Web.config' 

  
ForEach ($File in $Files)
{
 write-host 'Processing Server: '   $File 
 (Get-Content -Path $File) | ForEach-Object {$_ -replace 'MyOldSQLINSTANCE, 1234', 'MyNewSQLINSTANCE, 5678' } | Set-Content $File
}
write-host ' Finished'




Get All Web Server Names
$servers = 'MyWebServer01','MyWebServer02','MyWebServer03',' MyWebServer04','MyWebServer05'
  
set-item WSMan:\localhost\Client\TrustedHosts -Value "MyWebServer01, MyWebServer02, MyWebServer03, MyWebServer04, MyWebServer05"
  
set-executionpolicy remotesigned

$Username  = read-Host  "Enter Web Server Login"
$Password  = read-Host -AsSecureString   "Server Password"
  
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$password

ForEach ($server in $servers)
{
    invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 

    {
   
     import-module -Name webadministration
     Get-WebConfiguration -Filter "/system.webServer/httpProtocol/customHeaders/Add/." 

     -Location "C:\Windows\System32\inetsrv\config" | Where-Object { $_.name -eq 

     "X-serverName" } | Select-Object value
                        
    }
}

 

Get WebSite Application Pool
$servers ='MyWebServer01','MyWebServer02','MyWebServer03','MyWebServer04','MyWebServer05'

$Username  = read-Host  "Enter Server Login"
$Password  = read-Host -AsSecureString   "Server Password"

$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$Password

  ForEach ($server in $servers)
  {
       invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 

       {
   
         # Load IIS module:
         Import-Module WebAdministration
         # Set a name of the site we want to recycle the pool                  

         $site = "www.myWebSite.com/MyVirtualFolder/"
         # Get pool name by the site name:
         $pool=(Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
         write-host $using:server , $Site,  $pool  -f Cyan                        
       

       }
    }

  

Search File Contents on All Web Servers
set-item WSMan:\localhost\Client\TrustedHosts Value "MyWebServer01, MyWebServer02, MyWebServer03, MyWebServer04, MyWebServer05"

set-executionpolicy remotesigned

$servers = MyWebServer01','MyWebServer02',' MyWebServer03',' MyWebServer04','MyWebServer05'

$Username  = read-Host  "Enter your Windows Login: "
$Password  = read-Host -AsSecureString   "Windows Server Password:"

$file = "C:\inetpub\MyWebsite\MyPhysicalFolder\web.config"  #File to search#>
$SearchString = "add type"    #Text to find in file#>

   
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$password

    ForEach ($server in $servers)
    {
       invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 

       {
   
        import-module -Name webadministration
        Get-Content -Path $using:file  | WHERE { $_ -Match $using:SearchString }
                        
       }

    }
____

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.