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
LIC_AGREEMENT=ACCEPT
FILE=C:\Program Files\IBM\SQLLIB\
INSTALL_TYPE=CUSTOM
COMP=BASE_CLIENT
COMP=DOTNET_DATA_PROVIDER
COMP=SPATIAL_EXTENDER_CLIENT_SUPPORT
COMP=JDBC_SUPPORT
COMP=JDK
COMP=LDAP_EXPLOITATION
COMP=ODBC_SUPPORT
COMP=OLE_DB_SUPPORT
COMP=REPL_CLIENT
COMP=SQLJ_SUPPORT
LANG=EN
LANG=DE
INSTANCE=DB2
DB2.NAME=DB2
DEFAULT_INSTANCE=DB2
DB2.TYPE=CLIENT
DB2_EXTSECURITY=NO
DB2_USERSGROUP_NAME=DB2USERS
DB2_ADMINGROUP_NAME=DB2ADMNS
DB2_COMMON_APP_DATA_TOP_PATH=C:\ProgramData
DB2_COPY_NAME=DB2COPY1
DEFAULT_COPY=YES
DEFAULT_CLIENT_INTERFACE_COPY=YES
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 off
echo "start main.cmd"
time /T
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
md %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLDBDIR
md %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR
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 😊