始めに
社内でMicrosoft365を導入し、慣れてきたため一段上の利用方法ができないと模索していました。
現在、手作業でExcel形式で展開している社内データ（売上日報、商品データetc）について自動化できないかと考えて実装した内容になっています。
内容
全体のプロセスは以下の様になります。
1.SQLCMDにてオンプレSQLServerからデータをCSV形式で出力
2.PowerShellにてオンプレサーバーからSharePointOnlineへファイルをアップロード
⇒1&2はbatファイルにてKickさせることでタスクスケジューラでの時刻起動を可能とする
実は今回の内容は全体のプロセスとしては前半部分になります。
長くなってしまうため、以下の部分は別途投稿予定といたします。
3.PowerAutomateにてCSVの内容をExcelのテーブルに転記
4.PowerAutomateにてExcelの内容更新を検知しTeamsにて通知
環境
■WindowsServer2012R2
■SQLServer2016
■PowerShell PSVersion5.1.14409.1005
構成
■SQLServer
C:
∟aaa
∟getURIAGE_P.sql
∟upload_URIAGE_P.ps1
∟URIAGE_P.bat
∟URIAGE_P.CSV
■SharePoint
サイトURL
∟Shared%20Documents/Folder1
∟URIAGE_P.CSV
コード
WIndows.bat
sqlcmdの結果を直接CSVに出力するのではなく一度tempファイルに出力し2行目の仕切行を消すのがミソです。
@echo off
REM #####################
REM ★接続情報
REM #####################
set svname='サーバーの名前（シングルクォートとかなにもいらないよ）'
set dbname='DBの名前（シングルクォートとかなにもいらないよ）'
set user='SQLユーザー（シングルクォートとかなにもいらないよ）'
set password='SQLパスワード（シングルクォートとかなにもいらないよ）'
REM tempファイルのパス
set tempFilePath='C:\aaa\temp.csv'
REM 実行するsqlのパス
set inputsql='C:\aaa\getURIAGE_P.sql'
REM 出力するCSVのパス
set outputPath='C:\aaa\URIAGE_P.CSV'
REM PowerShellを実行するパス
set exePath='C:\aaa\upload_URIAGE_P.ps1'
REM SELECT文を実行しtempファイルへ出力 ※区切り文字をカンマ、余分なスペースを削除
sqlcmd -S %svname% -d %dbname% -U %user% -P %password% -i %inputsql% -b -s "," -W -o %tempFilePath%
REM tempファイルの先頭が「-」でない行をCSVファイルへ出力 ※2行目の「---」を取り除く
findstr /V "^-" %tempFilePath% > %outputPath%
REM tempファイルを削除
del %tempFilePath%
REM PowerShellによってファイルをSharePointへ送る
powershell %exePath%
exit
getURIAGE_P.sql
SQLの内容は参考にならないと思われるため割愛させていただきます。
1行目に「set nocount on」をセットすることだけ注意してください。
upload_URIAGE_P.ps1
TLS1.2有効化これが一番のハマったポイントでした。
SQLServer側で実行すれば継続的に設定が生きると思い込んでいたのですが一時的なものだとは…。
PSの初めに記載することをお勧めします。
# TLS1.2有効化 WindowsServerによってはTLS1.2が有効でない場合がある
[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12
# SharepointOnline用のCSOMをロードする
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") > $null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") > $null
$UserName = '自分のMicrosoftアカウントを@~.comまで"で囲って入れてね'
$Password = ConvertTo-SecureString '自分のパスワードを"で囲って入れてね' -AsPlainText -Force
$SiteURL = 'SiteURLを"で囲って入れてね 大抵"https~\sites\~"になるよ'
# 認証
$credential = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $Password)
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Context.Credentials = $credential
$objWeb = $Context.Web
$Context.Load($objWeb)
$Context.ExecuteQuery()
# ドキュメントライブラリ上のアップロード先フォルダをロードする
$UploadFolder = 'フォルダーの場所を"で囲って入れてね 大抵"Shared%20Documents~"になるよ'
$Ufolder = $objWeb.GetFolderByServerRelativeUrl($objWeb.ServerRelativeUrl + $UploadFolder)
$Context.Load($Ufolder)
$Context.ExecuteQuery()
# ローカルサーバ側のアップロード元ファイルを定義
$Path = "C:\aaa"
$FileName = "URIAGE_P.CSV"
$TargetFile = Join-Path $Path $FileName
$LocalFile = Get-ChildItem $TargetFile
# sharepoint側のファイルストリームを開いてファイルをアップロード（Overwiteは必要に応じて変更）
$FileStream = New-Object System.IO.FileStream($LocalFile,[System.IO.FileMode]::Open)
$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$FileCreationInfo.Overwrite = $true
$FileCreationInfo.ContentStream = $FileStream
$FileCreationInfo.Url = (Get-ChildItem $TargetFile).BaseName + (Get-ChildItem $TargetFile).Extension
$UpLoad = $UFolder.Files.Add($FileCreationInfo)
$Context.Load($UpLoad)
$Context.ExecuteQuery()
$FileStream.Close()
$Context.Dispose()
exit 0
感想
コードの欄にも書きましたがTLS設定の部分でガッツリ躓きました。
何回もPowerShellの再インストールや環境構築をし直す中で、たまたまWindows10の環境にて試した際にTLSのバージョン違いに気が付きました。
やはりITはネットワークの知識も含めた総合力が大切だなと身に染みて感じました。
無事にアップロードできたCSVファイルの内容を元にPowerAutomateにて加工し投稿する部分はまた別途投稿予定です。
以上