今回は、Azure Data Factory のAzure-SSIS Integration Runtime という機能を使って、 Azure 上でSSIS プロジェクトを実行する方法について解説します。
CData SSIS Components を使ったSSIS プロジェクトを、Azure Data Factory にデプロイすることで、Azure クラウド上でSSIS を使った ETL/EAI の仕組みを構築することができます。
１．必要なもの
・Azure アカウント
・Microsoft Visual Studio 2022
・Microsoft SQL Server Management Studio (以下 SSMS )
・Power Shell 6 以降
・Azure Powershell
・CData SSIS Components
・CData SSIS Components 用RTK
２．今回のシナリオ
今回は、SSIS Components for Excel と、SSIS Components for Kintone の2 つのSSIS Components を使い、SharePoint のドキュメントフォルダーにあるExcel ファイルからデータを読み込み、kintone アプリのレコードへUpsert を行います。
３．初期設定
初めに、SSIS プロジェクトを作成するために必要な設定を行います。
1) Visual Studio 2022 のインストール
Visual Studio 2022 IDE - ソフトウェア開発者向けプログラミング ツール
2) 拡張機能のインストール
Visual Studio の「拡張機能(X)」メニューから「拡張機能の管理(M)...」で、拡張機能マネージャーを開きます。
「Microsoft Integration Services Projects」を検索し、「インストール」をクリックします。
3) CData SSIS Components のインストール
使用するSSIS Components をインストールします。
Azure Data Factory で実行する場合は、ターゲットとするSQL Server のバージョンで、Azure Data Factory と、SQL Server 2017 が選択されていることを確認します。
Azure Data Factory でSSIS Components を使用する場合は、RTK が必要になります。
RTK の発行は、弊社サポート へお問い合わせください。
4) PowerShell 6 以降のインストール
Azure PowerShell を使うために、PowerShell のバージョンを確認し、もしバージョンが6 以前だった場合は、6 以降をインストールします。
Windows への PowerShell のインストール - PowerShell | Microsoft Learn
PowerShell のバージョンは、PowerShell で「$PSVersionTable」を実行すると確認できます。
5) Azure PowerShell のインストール
Azure PowerShell をインストールします。
Install Azure PowerShell on Windows | Microsoft Learn
6) Microsoft SQL Server Management Studio のインストール
SQL Server Management Studio (SSMS) のダウンロード - SQL Server Management Studio (SSMS) | Microsoft Learn
４．Azure の設定
1) Azure への接続
Azure PowerShell で、Azure へ接続します
Connect-AzAccount
Connect-AzAccount (Az.Accounts) | Microsoft Learn
2) リソースグループの作成
リソースグループを作成します。
New-AzResourceGroup -Name SSISDemoResourceGroup -Location EastUS
New-AzResourceGroup (Az.Resources) | Microsoft Learn
3) SQL Server の作成
New-AzSqlServer -ResourceGroupName "SSISDemoResourceGroup" -ServerName "ssisdemosqlserver" -Location "EastUS" -SqlAdministratorCredentials (Get-Credential) -PublicNetworkAccess "Enabled"
New-AzSqlServer (Az.Sql) | Microsoft Learn
4) SQL Server のネットワーク設定
ネットワークのファイアウォール規則に、Visual Studio がインストールされているPC から接続する際のIP アドレスを追加して接続を許可します。
New-AzSqlServerFirewallRule -ResourceGroupName "SSISDemoResourceGroup" -ServerName "ssisdemosqlserver" -FirewallRuleName "Rule01" -StartIpAddress "xxx.xxx.xxx.xxx" -EndIpAddress "xxx.xxx.xxx.xxx"
New-AzSqlServerFirewallRule -ResourceGroupName "SSISDemoResourceGroup" -ServerName "ssisdemosqlserver" -FirewallRuleName "AllowAllWindowsAzureIps" -StartIpAddress "0.0.0.0" -EndIpAddress "0.0.0.0"
New-AzSqlServerFirewallRule (Az.Sql) | Microsoft Learn
5) ストレージ アカウントの作成
SSIS Components をアップロードするストレージアカウントを作成します。
New-AzStorageAccount -ResourceGroupName "SSISDemoResourceGroup" -Name "ssisdemostorage" -Location EastUS -SkuName Standard_LRS -Kind StorageV2
New-AzStorageAccount (Az.Storage) | Microsoft Learn
6) ストレージ アカウントのネットワーク設定
Public アクセスを許可します。
Set-AzStorageAccount -ResourceGroupName "SSISDemoResourceGroup" -Name "ssisdemostorage" -PublicNetworkAccess "Enabled" -AllowBlobPublicAccess $true
Set-AzStorageAccount (Az.Storage) | Microsoft Learn
５．SSIS Components のデプロイ
使用したいSSIS Components が複数ある場合は、それぞれのlib フォルダーにあるdll フ ァイルを一つのlib フォルダーにコピーしてまとめます。
lib フォルダーのAzureDeploy.ps1 を実行し、lib フォルダーのファイルをストレージ アカウントにアップロードします。
.\AzureDeploy.ps1 -ResourceGroupName "SSISDemoResourceGroup" -SubscriptionId "xxxxx" -SqlServerName "ssisdemosqlserver.database.windows.net" -SqlDatabaseUser "xxxxx" -SqlDatabasePwd "xxxxx" -DataFactoryName "SSISDemoFactory" -StorageAccountName "ssisdemostorage"
これで、Azure Data Factory でSSIS を実行するための準備が完了しました。
次は、実行したい SSIS のプロジェクトを作成します。
６．SSIS プロジェクトの作成
Azure Data Factory で実行する場合も、SSIS プロジェクトの作成手順はローカルで実行する場合とほぼ変わりません。
プロジェクトの設定で異なる点は、プロジェクトのプロパティにある「Azure 対応の設定」の「Azure-Enabled Project 」がTrue になっている点です。
1) プロジェクトの作成
プロジェクト テンプレートから「Integration Services Project(Azure-Enabled) 」を選択し、新しいプロジェクトを作成します。
2) フローの作成
通常のSSIS プロジェクトと同様に、フローを作成します。
今回作成するフローでは、SharePoint のドキュメントフォルダーに格納されているExcel ファイルから、シートにあるデータを読み込み、kintone アプリのレコードにUpsert を行うため、接続マネージャーでExcel とkintone への接続を作成します。
3) Excel への接続設定
Excel ファイルはSharePoint 上にあるため、下記のように設定します。
Azure Data Factory でSSIS プロジェクトを実行する場合、接続時のOAuth 認証でブラウザを表示することができないため、今回はヘッドレスマシンの方法で、予めOAuth 認証を行い、アクセストークンとリフレッシュトークンを接続文字列に指定しました。
CData SSIS Components for Microsoft Excel - SharePoint Online への接続
Connection Type = SharePoint REST
URI =sprest://remotePath/file.xlsx
Storage Base URL = xxxxx
Initiate OAuth = REFRESH
OAuth Access Token = xxxxx
OAuth Refresh Token = xxxxx
RTK = xxxxx
CData SSIS Components for Microsoft Excel - SharePoint Online への接続
4) kintone への接続設定
kintone への接続は、下記のように設定します。
URL = https://<サブドメイン>.cybozu.com/
User = xxxxx
Password = xxxxx
RTK = xxxxx
また、今回はサブテーブルへはアクセスしないため、「Check For Subtables In=None」を指定しています。
CData SSIS Components for Kintone - CheckForSubtablesIn
5) データ フローの作成
制御フローにデータフロー タスク を追加し、下記のようにExcel Source から Kintone Destination へデータを渡すフローを追加します。
CData Excel Source で、対象となるExcel シートのテーブルを選択します。
Excel シートのカラムとkintone アプリのカラムでは、カラム長が異なるため、派生列で長さを調整しています。
CData Kintone Destination で、出力先のkintone アプリを選択し、Mappings で、Excel のカラムをどのカラムに紐づけるかを設定します。
７．Azure へのデプロイ
1) Azure への接続
Visual Studio がログインしていることを確認します。
プロジェクトを右クリックし、「Azure の SSIS」から「Azure で SSIS に接続(O)」をクリックします。
右上にある「Sign In」をクリックします。
Azure Subscription 、Azure Service 、Data Factory 、Integration Runtime 、Azure Storage を選択し、「Connect 」をクリックします。
2) プロジェクトのデプロイ
プロジェクトを右クリックし、「展開(Y) 」をクリックします。
「配置ターゲットを選択する」で、「Azure Data Factory での SSIS 」を選択し、「Next >」をクリックします。
配置先のSQL Server のサーバー名とログイン、パスワードを入力し、「接続」をクリックします。
接続されると、「パス」が有効になるので、「参照(B)... 」をクリックして、プロジェクトを配置するフォルダーを指定します。
設定内容を確認し「配置(D) 」をクリックします。
８．プロジェクトの実行
SSMS から作成したSQL Server へ接続し、デプロイした SSIS プロジェクトを右クリックして、「実行(X)... 」をクリックします。
「OK 」をクリックします。
実行 レポートを確認します。
９．まとめ
Azure Data Factory でデータ連携を行う際は、SSIS Componets を活用してみてください。
