In Microsofts Azure Cloud the Azure Data Factory (ADF) is the state of the art tool to transfer higher amounts of data between database systems. To leverage a compability with classic ETL project, there is also an Azure Data Factory SSIS-Integration Runtime (ADF SSIS-IR).
The SSIS-IR is running on one or several virtual machines, depending on chosen tarif, as a variant of SQL Server Integration Services (SSIS). As all other PaaS you do not need to care about patching or managend of operation system and application, which also means there is no direct access to the operation system.
This can lead to challenges when installing drivers.
To install extensions and drivers you need to upload installation files and scripts to Blobstorage-Account. For a series of drivers, there are samples available on GitHut, the process is briefly descriveb at Microsoft Docs.
Unfurtanetly there is no sample for installing IBM DB2 drivers.
To install these drivers, you need:
- Access to IBM DB2 Installation medium
- Access to a system with administration rights
At first, you install the DB2 driver locally or on a VM, to create all the configuration files required for an unattended installation.
The following screenshots show some steps of the installation (Text is in German):
After finishing a file PROD_CLIENT.RSP is generated, you need to put this file into the installation of the driver in SSIS-IR.
In my sample the content looks like this:
PROD_CLIENT.RSP needs to be uploaded together with main.cmd, which looks in my sample as follows:
The DB2-installation package is placed in the subfolder DB2.
Code of main.cmd:
echo "start main.cmd"
echo "Installation DB2 64 Bit Treiber"
%cd%\DB2\setup.exe /u %cd%\PROD_CLIENT.rsp /l .\output.log
echo "Setup Completed"
net stop DB2MGMTSVC_DB2COPY1
copy %cd%\DB2\SQLDBDIR\*.* %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLDBDIR\
copy %cd%\DB2\SQLNODIR\*.* %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR\
net start DB2MGMTSVC_DB2COPY1
echo "Configuration Completed"
In the script you also see server configuration to be imported - but this needs to be created first.
Executing db2cmd.exe in Azure SSIS-IR is not possible, since it opens a second interactive window.
The solution for this is quite simple. You just need to configure all nodes and catalogs locally. After executiong all db2 catalog and db2cmd commands the configuration is saved in SQLDBDIR and SQLNODIR.
These can be placed in the DB2 subfolder on Azure blob storage and can be copied by main.cmd inside the SSIS-IR. First the DB2 management service needs to be stopped.
After starting it again, accessing DB2 from SSIS is working 😊