FTPで送られてくるXMLファイルをSQLServerに自動的にロードする

• Goal
  
  FTPで送られてくるXMLファイルを自動的にSQLServerのテーブルにロードする。
  SQLServer上にテーブル名Logxmlを作成し、受信したXMLを1カラムにINSERTする。LogxmlテーブルのSQLは以下の通り。この後、必要に応じてXML形式のカラムからトリガーやTSQLなどで各要素・カラムの切り出しをする。
  
  

  	CREATE TABLE [dbo].[Logxml](
  	[ID] [int] IDENTITY(1,1) NOT NULL,
  	[logxml] [xml] NULL,
  	[filename] [varchar](500) NULL,
  	[creation_date] [datetime] NULL,
  	CONSTRAINT [PK_Logxml] PRIMARY KEY CLUSTERED
  	(
  	[ID] ASC
  	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  	GO

  view raw create_logxml.sql hosted with ❤ by GitHub
  
  
  
  
  
• How
  
  PowerShellで実施。
  ターゲットのフォルダから対象のファイル名を取得して、Bulkインサートを行う。
  スクリプトは常駐させておいて、60秒間隔で起動。
  （本当はWMIでやりたいので、後日トライ予定）
  
  

  	#一定時間間隔で起動
  	while(1){
  	#終了時は、[Ctrl + C] を押す
  	Write-Host "End to [Ctrl + C]"
  	#Connect to the SQL database
  	$SQLserver = "0.0.0.0" -- SQL server name
  	$Icatalog = "TEST" -- Database name
  	$UID = "UID"
  	$PASSWORD = "PASSWORD"
  	$conn = New-Object System.Data.SqlClient.SqlConnection
  	$conn.ConnectionString = "Server = $SQLserver; Database = $Icatalog; User ID = $UID; Password= $PASSWORD;"
  	$Conn.Open()
  	$Command = New-Object System.Data.SQLClient.SQLCommand
  	$Command.Connection = $Conn
  	#FTPで受信するフォルダPathの設定
  	$filepath = "C:\inetpub\ftproot\test"
  	#フォルダ内の全てのXMLファイルをリスト化
  	$filelist = Get-ChildItem -name $filepath\*.xml
  	#リストの上から順番にLoad
  	foreach ($filename in $filelist){
  	$fileinput = Get-Content $filepath\$filename
  	echo "Loading $filename"
  	$output | foreach {
  	$Command.CommandText = "INSERT INTO dbo.Logxml(logxml,filename,creation_date) SELECT CONVERT(XML, BulkColumn) AS BulkColumn,'$filename',getdate() FROM OPENROWSET(BULK '$filepath\$filename', SINGLE_BLOB) AS X"
  	$Command.ExecuteNonQuery() | out-null
  	}
  	#アーカイブフォルダーへファイルの移動
  	Move-Item $filepath\$filename $filepath\archive
  	}
  	$Conn.Close()
  	#60秒待機
  	Start-Sleep -Seconds 60
  	}

  view raw XML_auto_load.ps1 hosted with ❤ by GitHub