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

DB2 script to Remotely execute SSIS Packages

In preparation to allow DB2 job to remotely execute SSIS packages several configurations need to be put in place mostly on Windows side.

1.
    Create a .BAT file on the Windows servers and apply the following code. (Include the disk letter)
    Driver Letter:Call osql.exe -Q"execute msdb.dbo.sp_start_job [My SQL Agent Job]" 
     -S MySQLServer -U My SQL account -P My Password

2.
    Create a Windows account that has execute permissions to the .BAT file.

3.
    DB2 command to remotely execute the SSIS Package.

              RUNRMTCMD  CMD('I:\SSISJobs\ExecuteMySQLJob.BAT') +                                
              RMTLOCNAME('111.111.111.111' *IP) +                             
              RMTUSER('MyWindowsUser') RMTPWD('password') +                   
              CCSID(1252) 

    You will need to include the full path of the Batch file, IP address of the windows server, Windows      user account and password.

There are several other ISeries DB2 commands that can be used as an alternative to RUNRMTCMD

but this is sufficient to handle the task. 

4.
    Install the following IBM ISeries Client service. This needs to be enabled on the Windows Server.



5.
    Allow port 512 to be opened on the Windows server firewall.