PowerShellのループ処理

PowerShellを使ったループ処理のサンプルを作成する。
お題：任意のディレクトリ内のファイルについて列挙する。
※SQL Serverのインストールディレクトリ内の「DATA」フォルダを参照するサンプルを作成する。




まずはディレクトリを移動

PS C:\Users\kimiyuki> cd "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA"

PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 

ディレクトリ内のファイルの列挙
・Get-ChildItemでディレクトリ以下のファイルを列挙出来る。
　-Includeオプションで拡張子を指定している。

PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 
Get-ChildItem -Recurse -File -Include *.mdf


    ディレクトリ: C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA


Mode                LastWriteTime     Length Name                                                                                                                                                                                 
----                -------------     ------ ----                                                                                                                                                                                 
-a---        2019/01/03     20:55  130088960 AdventureWorksDW2012.mdf                                                                                                                                                             
-a---        2019/01/03     20:55   23003136 AdventureWorksLT2012.mdf                                                                                                                                                             
-a---        2019/01/03     20:55    5111808 master.mdf                                                                                                                                                                           
-a---        2019/01/03     20:55    4259840 model.mdf                                                                                                                                                                            
-a---        2019/01/03     20:55   17498112 MSDBData.mdf                                                                                                                                                                         
-a---        2019/01/20      5:10  148897792 MyStockDB.mdf                                                                                                                                                                        
-a---        2019/01/05      2:10   12582912 SalesDB.mdf                                                                                                                                                                          
-a---        2019/01/03     20:55   41943040 SSISDB.mdf                                                                                                                                                                           
-a---        2019/01/03     21:01    8388608 tempdb.mdf                                                                                                                                                                           
-a---        2019/01/03     20:55   14680064 TestDB.mdf       

※列挙するだけならGet-ChildItemコマンドで十分であるが、
ループ毎に何らかの処理をしたい場合は不十分…

foreachによるループ処理
・Get-ChildItemの結果を$array にObject型の配列として格納する。
・LastWriteTimeはDateTime型なのでToStringでフォーマットを指定できる。

PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 
$array = (Get-ChildItem -Recurse -File -Include *.mdf )
foreach($a in $array) 
{
    echo $a.LastWriteTime.ToString("yyyyMMdd_HHmmss")  
} 
    
20190103_205520 
20190103_205518 
20190103_205524 
20190103_205522 
20190103_205522 
20190120_051006 
20190105_021043 
20190103_205517 
20190103_210146 
20190103_205518 

※foreachのループ処理部分で個別のロジックの記述が可能。
感覚的にはC#のループ処理と同じイメージ。


foreach-objectによるループ処理
foreachの例と同様にGet-ChildItemの結果を加工する。

PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 
Get-ChildItem -Recurse -File -Include *.mdf | ForEach-Object -Begin{$array= @();} -Process{$array += $_.LastWriteTime.ToString("yyyyMMdd_HHmmss")} -End{$array}

20190103_205520
20190103_205518
20190103_205524
20190103_205522
20190103_205522
20190120_051006
20190105_021043
20190103_205517
20190103_210146
20190103_205518

※ForEach-Object コマンドレットでは、パイプに渡して処理を書くので１行で収まっている（可読性は悪い気が…）
-Beginオプション：前処理を記述（データを格納する配列を宣言）
-Processオプション：処理内容を記述（配列にデータを格納）
　「$_」で元のデータにアクセスできる
-Endオプション：後処理（結果の表示）

まとめ

色々調べると、foreachステートメントよりforeach-objectコマンドレットの方がメモリ量が少なくて済むそうなので処理の高速化が期待できる。
でも、使ってみるとforeachステートメントの方がC#等のループ処理に近い感覚で記述できるので分かりやすい気がします。
この辺の感じは未だにラムダ式に慣れずクエリ式でLinqを書いている感覚に近いものがあります…

参考

Powershellのforeach(ForEach-Object)の使い方

【ForEach-Object】PowerShellでパイプからforeachループするテクニック

PowerShellの落とし穴！ForEach-Objectとforeachは違う？

PowerShellのGet-ChildItemコマンドレットでファイル名の一覧を取得する（応用編）

SQL Server のIndexの効果検証2

前回の投稿で単純なSELECTクエリに対してIndexの効果を検証した。
今回は完全一致、部分検索（Where句で範囲を指定）についてNon-Clustered Indexの複合Index、付加Indexの作成と効果検証を行う。

環境

※前回の投稿と同じ

• DB Instance： SQL Server 2017 (14.0.2002.14) Developer
• DB： AdventureWorksLT2012 から Import Wizard でデータをImportして作成したDB(SalesDB)

検証対象のTable： SalesLT.Product
Base Query:

SELECT ProductID, ProductNumber, Color 
FROM SalesLT.Product

ProductID	ProductNumber	Color
680	FR-R92B-58	Black
706	FR-R92R-58	Red
707	HL-U509-R	Red
708	HL-U509	Black
709	SO-B909-M	White
710	SO-B909-L	White
…	…	…

計：295行
※元のAdventureWorksLT2012 にはIndexの設定があるが、データをImportしたDBにはIndexの設定は無い。
（スキーマは移る）
Index作成後にQueryをテストする前にキャッシュのクリアと統計情報の出力ために下記クエリを実行する。

USE[SalesDB]

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;
GO

テストするNon-Clustered Indexの種類は以下の通り

• ProductIDのみ（前回テスト済）
• 複合Index：ProductID, ProductNumber, Color の3列を複合Indexとして設定
• 付加列Index：ProductIDをIndex Keyとして設定し、付加列としてProductNumber, Color を設定

テスト1（完全一致）

以下の完全一致クエリ（結果セットが1行）に対して、Non-Clustered Index の構成を変えて実行プランを比較する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID = 714

統計情報の結果

STATISTICS IO	ProductIDのみ	複合Index	付加列Index
スキャン回数	0	1	0
論理読み取り数	3	2	2
物理読み取り数	1	0	0
先行読み取り数	0	0	0

実行プランのコスト比較

	ProductIDのみ	複合Index	付加列Index
完全一致（1行）	0.0065704	0.0032831	0.0032831

• 複合Indexと付加列Indexではコストが同じ
• ProductIDのみのIndexに比べコスト比：50%

メモ

• 複合Indexと付加列Indexでコストが同じになる。（今回の例では？）
• 付加列Indexの方がスキャン回数が少なくなるので規模が大きくなると有利か。
• 複合Index、付加列Indexの場合は「RID Lookup」が不要になる。

テスト2（部分検索）

以下の部分検索クエリ（結果セットが5行）に対して、Non-Clustered Index の構成を変えて実行プランを比較する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID Between 710 AND 714;

統計情報の結果

STATISTICS IO	ProductIDのみ	複合Index	付加列Index
スキャン回数	1	1	1
論理読み取り数	7	2	2
物理読み取り数	1	0	0
先行読み取り数	0	0	0

実行プランのコスト比較

	ProductIDのみ	複合Index	付加列Index
部分検索（5行）	0.0164145	0.0032875	0.0032875

• 複合Indexと付加列Indexではコストが同じ
• ProductIDのみのIndexに比べコスト比：20%

メモ

• 完全一致の場合と同じく複合Indexと付加列Indexでコストが同じになる。（今回の例では？）
• 完全一致より部分検索の方が複合Index、付加列Indexの効果が大きい。
• 複合Index、付加列Indexの場合は「RID Lookup」が不要になる。

まとめ

今回の事例では、パフォーマンス的に複合Indexと付加列Indexは同等になっている。Indexの構成を考えると、付加列Indexの方がIndexサイズが小さく出来るので付加列Indexの方がよいのかと思う。
今回の事例でIndexのサイズをシステムストアドプロシージャ「sp_spaceused」で計算すると、どちらも 1040KBとなってしまう…
（テーブルのサイズが小さいことが要因か？）
・Indexのサイズ計算（sp_spaceused）

DECLARE @Object_Name nvarchar(1000) = '';
USE SalesDB  
SET @Object_Name =  N'SalesLT.Product';

EXEC sp_spaceused @Object_Name;

今回の事例のようにSELECT文の結果セットを包含するIndexのことを「カバリングインデックス」と呼ぶ。
カバリングインデックスにすることでリーフページからデータを参照することがなくなるので、RID Lookupが削減できるということらしい。
実際、上記Index（ProductID, ProductNumber, Color）以外の列をSELECT文に追加するとパフォーマンスが悪化する。（下図参照）

結果セットを増やした場合

SELECT  ProductID,  ProductNumber, Color, Size,  SellStartDate
FROM  SalesLT.Product
WHERE  ProductID  Between 710 AND 714;

複合IndexでProductID, ProductNumber, Color の3列を設定している状態で、結果セットとして「Size」、「SellStartDate」を追加すると実行プランでは RID Lookupが発生してしまい実質的には「ProductID」のみをNon-Clustered Indexとした場合と同じになる。
複合Index、付加列Indexは上手く設定できればパフォーマンスの向上が図れるが結果セットの作成側（アプリケーション開発側）との調整が必要かと。
また、現実的には単独のTableからの結果セットではなく、複数のTableの結合、集計が組み合わされるのでトータルでどのようにIndexを付与するのが良いのかは難しい問題と思います。
DB管理者側の視点だけでは意味がないので…
（今までちゃんとしてこなかった事への言い訳）

参考

SQLServerのインデックスについてざっくりとまとめてみた
付加列インデックスの作成

Microsoft Excel 2010 (KB4461627) 32 ビット版 の更新プログラム 不具合について

2019/1/2 にリリースされているExcel 2010 の更新プログラム：KB4461627
について 1/3にインストールしたところExcel 新規起動後にセルに文字列を記入、若しくは文字列が記入済のExcelを開くと「動作を停止しました」とのこと。
※既存のExcelの場合は起動してすぐに落ちます…
ちょっと調べると類似の不具合報告が上がっているようなので、
更新プログラムのアンインストールで対応し問題が解消されたことを確認しました。
暫く、KB4461627 の更新は避けようと思います…

環境

Windows 7 Professional SP1 x64
Model: HP h8-1180jp
CPU: Intel Core i7-2600 @ 3.40 GHz
Memory: 16GB
Office: Microsoft Office Professional 2010
Excel Version: 14.0.7224.5000 (32bit)


・KB4461627 インストール後の更新履歴




・Excelのエラー状態（起動後にセルに文字を入力で発生）




・インストールされた更新プログラムの削除（アンインストール）

• 下記手順で「更新プログラムのアンインストール」画面を表示する。
  コントロールパネル→プログラムと機能→インストールされた更新プログラム
• 右上の検索ウィンドウで「KB4461627」と入力しインストールされていることを確認する。
• 右クリック→アンインストール
  ※今回の更新はOSの再起動は不要なようです。

アンインストールが終わったらExcel起動できます。
※再度インストールされてしまうことを嫌う場合は「WindowsUpdateのインストールする更新プログラムの選択」画面で対象の更新プログラム（この場合はKB4461627）を右クリック→「更新プログラムの非表示」を選択してください。
再度インストールしたい場合はWindowsUpdateの左ペインから「非表示の更新プログラムの再表示」からチェックを入れてください。

更新プログラムの非表示

非表示の更新プログラムの再表示

参考

Excel 2010 (KB4461627)、2019 年 1 月 2日の更新プログラム


やれやれ…

SQL Server のIndexの効果検証1

SQL Server のIndexの設定有無によるSELECT文の実行プランを比較してみる。

環境

• DB Instance： SQL Server 2017 (14.0.2002.14) Developer
• DB： AdventureWorksLT2012 から Import Wizard でデータをImportして作成したDB(SalesDB)

検証対象のTable： SalesLT.Product
Base Query:

SELECT ProductID, ProductNumber, Color 
FROM SalesLT.Product

ProductID	ProductNumber	Color
680	FR-R92B-58	Black
706	FR-R92R-58	Red
707	HL-U509-R	Red
708	HL-U509	Black
709	SO-B909-M	White
710	SO-B909-L	White
…	…	…

計：295行
※元のAdventureWorksLT2012 にはIndexの設定があるが、データをImportしたDBにはIndexの設定は無い。
（スキーマは移る）
Index作成後にQueryをテストする前にキャッシュのクリアと統計情報の出力ために下記クエリを実行する。

USE[SalesDB]

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;
GO

テスト１（完全一致）

以下の完全一致のクエリ（結果セットが1行）に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。

USE[SalesDB]
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID = 714

統計情報の結果

STATISTICS IO	None	Clustered	Non Clustered
スキャン回数	1	0	0
論理読み取り数	101	2	3
物理読み取り数	0	0	1
先行読み取り数	101	0	0

実行プランのコスト比較

	None	Clustered	Non Clustered
1行検索Cost	0.0776806	0.0032831	0.0065704

• Clusterd Index のコスト比：4％
• Non-Clustered Indexのコスト比：8%

メモ

• Index無し(None)の場合はTable Scanが実行されすべてのPage(101)を読み取っている。
• Index有りの場合はCluster Index, Non-Cluster Index 共にIndex Scanが実行されており読取りPage数が非常に少なくなっている。 (Tableの規模が大きくなるほど効果が差が大きくなる)

テスト2（全件検索/OrderBy）

以下の全件検索クエリ（結果セットが295行、OrderBy付き）に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
ORDER BY ProductID

統計情報の結果

STATISTICS IO	None	Clustered	Non Clustered
スキャン回数	1	1	1
論理読み取り数	101	103	101
物理読み取り数	0	0	0
先行読み取り数	101	0	101

実行プランのコスト比較

	None	Clustered	Non Clustered
全件検索（295行：OrderBy）	0.0928176	0.0776806	0.0928176

• Clusterd Index のコスト比：84％
• Non-Clustered Indexのコスト比：100%

メモ

• Tableの全件検索ではIndex無し(None)とNon-Clustered Indexが同じ結果になる。（Non-Clustered Indexの効果が無い）
• Index無し、Non-Clustered Indexのコストを見るとTable Scan(84%) と Sort(16%)でコストが消費されている。
• Clusterd Index では、リーフページがProductIDでSortされているのでSortのコストがかからない。
• Clusterd Index ではコスト低減の効果があるが、1行検索のような大きな低減効果はない。

テスト3（部分検索）

以下の部分検索クエリ（結果セットが5行）に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。

SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID Between 710 AND 714;

統計情報の結果

STATISTICS IO	None	Clustered	Non Clustered
スキャン回数	1	1	1
論理読み取り数	101	5	7
物理読み取り数	0	0	1
先行読み取り数	101	0	0

実行プランのコスト比較

	None	Clustered	Non Clustered
部分検索（5行）	0.0776806	0.0040282	0.0164145

• Clusterd Index のコスト比：5％
• Non-Clustered Indexのコスト比：21%

メモ

• 部分検索の場合は Non-Clustered Index の効果が得られる。ただし、RID Lookupのコストが高いのでTableの規模が大きくなると悪化すると思われる。

まとめ

単純なSELECTクエリについてIndexの有無によるPage検索数、コストについて調べてました。
現実的にはTableのPrimary Key がClustered Index になってしまうので実運用上のIndexは Non-Clustered で作成する必要がありそうです。
Non-Clustered IndexについてはRID Lookupのコストをどのように下げるかが問題になりそうです。
これについては、複合Index、付加Indexを構築してカバーインデックスにすることで改善されるようです。その辺については別途まとめようと思います。

参考

なぜBTreeがIndexに使われているのか
SQLServerのインデックスについてざっくりとまとめてみた

SQL Server の Import Export Wizard によるデータのExport

SQL Server のBCPコマンドによるデータエクスポート の投稿でBCPコマンドを使ってTableデータをファイルに書き出す方法を説明している。
今回は、SQL Server のタスクに標準で備わっているデータエクスポートの機能を使ってTableデータをファイルに出力してみる。

環境

• DB： SQL Server 2012 SP4 (11.0.7001.0 (X64)) Evaluation or Express
• データソース： AdventureWorksLT2012
• データ保存先：ローカル ファイル（テキスト ファイル）

AdventureWorksLT2012 の SalesLT.Customer のデータをファイルに出力する。

Import/Export WizardによるデータExport

SSMSを起動しオブジェクトエクスプローラーからAdventureWorksLT2012を右クリック→タスク→データのエクスポート を選択する。





SQL Server インポートおよびエクスポート ウィザード
・ようこそ




・データソースの選択
認証：Windows認証
データベース：AdventureWorksLT2012




・変換先の選択
※データベース：AdventureWorksLT2012 の照合順序(SQL_Latin1_General_CP1_CI_AS) に対応したコードページを選択すること!!　（コードページの選択を誤ると実行時にエラーになる）

コードページの例（抜粋）

Identifier	.NET Name	Additional information
1252	windows-1252	ANSI Latin 1; Western European (Windows)
932	shift_jis	ANSI/OEM Japanese; Japanese (Shift-JIS)
65001	utf-8	Unicode (UTF-8)
51932	euc-jp	EUC Japanese

※ファイル名に出力先のファイルのフルパスを設定する。
ディレクトリを予め作成していること。




・テーブルコピーまたはクエリの指定




・フラットファイル変換先の構成
※元データとして SalesLT.Customer テーブルを選択する。
区切りは規定値を使用する。




・パッケージの保存および実行
設定内容をすぐに実行するか、SSISパッケージとして再利用可能なように保存するかを選択できる。
→ SSISパッケージとして保存できるのは、Evaluation または Standard以上の商用ライセンスの場合のみ。
今は「すぐに実行」を選択する。
※以下図は、Evaluation または Standard以上の商用ライセンスの場合




※Developer または Express等の無償ライセンスの場合




・ウィザードの完了
設定内容の概要を確認します。




・操作の完了
エラー無く処理が完了すると下図のような結果が表示されます。




・出力結果

コードページの選択エラー

上記例では元データとして AdventureWorksLT2012 を使用している。
AdventureWorksLT2012 の照合順序は SQL_Latin1_General_CP1_CI_AS であるため、対応したコードページとして「1252 (ANSI - ラテンI)」を選択しなければならないと説明している。
ここでは敢えてコードページを「932 (ANSI/OEM - 日本語 Shift-JIS)」に設定した場合で実行してみる。

・コードページを「932 (ANSI/OEM - 日本語 Shift-JIS)」に設定




・実行エラーが発生




・エラーメッセージ
→　コードページについて警告されている。




このように元のデータの照合順序と対応しないコードページを設定するとエラーとなり実行出来ない。

SSISパッケージの作成と再利用

上記事例ではExport処理を実施してしまうと操作を再利用することが出来ない。（もう一度ウィザードを始めから設定することになる）
Evaluation または Standard 以上の商用ライセンスの場合はSSISパッケージをファイルとして保存することが出来、コマンドプロンプトまたはPowerShellから実行することが出来る。
今回はEvaluation Editionの環境でSSISパッケージの保存とPowerShellによる実行を試してみる。

・SSISパッケージの保存設定1
保存先としてSQL Server 又はファイルシステムが選択できるが、今回はファイルシステムを選択。
保護レベルとパスワードを設定。




・SSISパッケージの保存設定2
保存先のディレクトリを設定する




・ウィザードの完了
設定内容の概要を確認します。




・操作の完了
エラー無く処理が完了すると下図のような結果が表示されます。




・SSISパッケージの実行
SSISパッケージの実行にはコマンドプロンプトまたはPowerShellでDTEXECコマンドを使用します。

DTEXEC /f “[SSISパッケージの保存パス(.dtsx)]” /Decrypt [パスワード]

※SQL Server をバージョンアップした環境で上記DTEXECコマンドを実行すると以下のエラーが発生します。

エラー: 2018-12-31 05:50:33.59 コード: 0xC0016020 ソース: 説明: エラー
0xC001700A “パッケージに含まれるバージョン番号が無効です。バージョン番号を現在のバージョン番号より大きくすることはできません。”
により、パッケージをバージョン 6 から 3 に移行できませんでした。

上記エラーはDTEXECコマンドに関連付けられているEXEの実体がSQL Server 2012ではない（古いVersion）になっていることが原因のようです。
レジストリを書き換えれば良さそうな気もしますが、SQL Server 2012に対応いたEXEを指定するようにコマンドを記載すれば動作します。

PS C:\Users\hoge> & "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /f  "C:\Temp\Export_Customer.dtsx" /Decrypt hogepin
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.7001.0 for 64-bit
Copyright(C) Microsoft Corporation. All rights reserved.

開始: 5:54:24
進捗状況: 2018-12-3105:54:26.00
   ソース: データ フロー タスク 1
   検証しています: 0% の完了
進捗状況の終了

~

進捗状況: 2018-12-3105:54:26.59
   ソース: データ フロー タスク 1
   クリーンアップ: 100% の完了
進捗状況の終了
DTExec: パッケージの実行から返されました DTSER_SUCCESS (0)。
開始: 5:54:24
完了: 5:54:26
経過時間:  2.059 秒

※EXEのフルパスを指定する場合に「Program Files」のように間にスペースが入るパスを指定する場合は、パス名の前に「&」を追加すること。

まとめ

• SSISを使ってデータをフラットファイルにExportする場合は、元データの照合順序と出力先のコードページの指定を正しく設定すること。
• 商用ライセンスではSSISパッケージを保存することが出来る。
• SSISパッケージを実行する場合は「DTEXEC」コマンドをPowerShell等で実行する。
• SQL Server のVersionが複数インストールされている環境では、DTEXECコマンドの指定でEXEのフルパスを指定すること。

追記(12/31)
SSISパッケージの保存で「ファイルシステム」ではなく、「SQL Server」を選んだケースを試してみたが、

• SSMSでIntegration Services にログインして「格納済のパッケージ」ディレクトリの「MSDB」の配下にSSISパッケージが格納されていることを確認。
• 実行したがエラーとなる。エラー内容から、Edtionの権限が認められていない模様。実行時はEvaluation Edition のインスタンスであったが、同じ環境下にExpress Edition のインスタンスが構築されており、その辺の問題かと。

• 上記のようにSSISパッケージをSQL Serverに保存したところ、以前の投稿で作成していた統合サービスカタログが初期化されてしまった…
  Import/Export Wizard で作成したSSISパッケージをSQL Serverに保存するのはやめた方が良さそう…

参考

Code Page Identifiers

DTExec.exe を使用してコマンド プロンプトから SSIS パッケージを実行する

SQL ServerのBulk Insertによるデータインポート

テキストファイルのデータを一括でTableにインポートする方法として
Bulk Insertを使ってみる。
AdventureWorksLT2012 の Customer Tableのデータを TestDB (ユーザー定義)のCustomer2 TableにInsertする例を作成する。
前提として、SQL Server のユーザー定義のDB：TestDB に AdventureWorksLT2012 のCustomer Tableと同じデータ型のTable：Customer2 を作成する。

環境

• SQL Server： SQL Server 2012 SP4 (11.0.7001.0 (X64))
  Evaluation or Express
• 元データ： AdventureWorksLT2012
• Import先：TestDB

Import用のTable作成

以下のCreate Table文を[TestDB]で実行する。

USE[TestDB]

IF OBJECT_ID('[dbo].[Customer2]', 'U') IS NOT NULL
  DROP TABLE [dbo].[Customer2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer2](
[CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NameStyle] bit NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] nvarchar(50) NOT NULL,
[MiddleName] nvarchar(50) NULL,
[LastName] nvarchar(50) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[CompanyName] [nvarchar](128) NULL,
[SalesPerson] [nvarchar](256) NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] nvarchar(25) NULL,
[PasswordHash] [varchar](128) NOT NULL,
[PasswordSalt] [varchar](10) NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_Customer_rowguid] UNIQUE NONCLUSTERED 
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Import用のデータをCSVファイルで作成

AdventureWorksLT2012 のCustomer TableのデータをCSVファイルにBCPコマンドを使って書き出す。（前回の記事を参照）
対象のデータに「,」が含まれている場合があるので、安易にカンマ区切りにしないこと。
データの区切り文字は適切に選択する。（今回は「;」を採用）

PS C:\Users\hoge> bcp AdventureWorksLT2012.SalesLT.Customer  out "c:\Temp\Customer.txt" -S 192.168.1.50\SQLEVA -T -c -t";"

コピーを開始しています...

847 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 16     平均 : (52937.50 行/秒)

出力結果

Bulk Insert

SSMSを起動し以下のクエリを実行

USE [TestDB]
TRUNCATE TABLE  Customer2;

BULK INSERT [dbo].[Customer2]
FROM 'c:\Temp\Customer.txt' 
WITH (FIELDTERMINATOR = ';', 
ROWTERMINATOR = '\n');

SELECT @@ROWCOUNT;

SELECT * FROM Customer2;

※WITH句の説明
FIELDTERMINATOR ：　列の区切りの指定
ROWTERMINATOR ：　行の区切りの指定


実行結果

参考

BULK INSERT (Transact-SQL)

CSV ファイルを BULK INSERT を使ってインポートする

まとめ

• Bulk Insertのデータ元ファイルはBCPコマンドで出力した場合のデータと同じく1行目からデータ行となっていること（列名のヘッダを含まない）。
• 列の区切り文字は文字列データに含まれない文字を使うこと（若しくは置換しておく）

SQL Server のBCPコマンドによるデータエクスポート

SQL Server 2012 でBCPコマンドを使ったデータエクスポートの手順をまとめる。
BCPコマンドの仕様詳細は以下のリンクを参照。
bcp ユーティリティ

bulk copy program ユーティリティ (bcp) では、 Microsoft SQL Server のインスタンスと、ユーザー指定の形式のデータ ファイルとの間でデータの一括コピーを行います。 bcp
ユーティリティを使うと、多数の新規行を SQL Server テーブルにインポートしたり、データをテーブルからデータ
ファイルにエクスポートしたりできます。 このユーティリティでは の知識は必要ありません。ただし、 queryout
Transact-SQLオプションと同時に使う場合はその知識が必要になります。
データをテーブルにインポートするには、そのテーブル用に作成されたフォーマット
ファイルを使用するか、テーブルの構造およびテーブルの列に有効なデータの型を理解しておく必要があります。

このようにBCPコマンドを使ってデータをDBにInsertしたり、DBからデータを取り出したり出来ます。
今回は、DBから指定のテーブルデータをテキストファイルに出力するサンプルを作成します。

環境

• DB： SQL Server 2012 SP4 (11.0.7001.0 (X64)) Evaluation or Express
• データソース： AdventureWorksLT2012
• データ保存先：ローカル ファイル（テキスト ファイル）

※BCPコマンドはEXPRESSでも使用できる。

サンプル1

AdventureWorksLT2012 のAddressテーブルのデータをテキストファイルに書き出します。
DB：AdventureWorksLT2012 (Evaluation)
Table：Address

Power Shell 実行内容

PS C:\Users\Hoge> bcp AdventureWorksLT2012.SalesLT.Address  out "c:\Temp\Address.txt" -S 192.168.1.50\SQLEVA -T -c -t","

コピーを開始しています...

450 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (450000.00 行/秒)

使用したオプション（大文字/小文字の区別がある）

コマンド	説明
out	出力するファイルのフルパス
-S	[ServerName][Instanse Name]
-T	Windows認証の場合に指定
-c	文字データとして処理する場合に指定
-t	列の区切りを指定

出力結果
※先頭行に列名が入らないことに注意



SQLEXPRESSの場合でも実行できる

PS C:\Users\Hoge> bcp AdventureWorksLT2012.SalesLT.Address  out "c:\Temp\Address_EXP.txt" -S hego-pc\SQLEXPRESS -T -c -t","

コピーを開始しています...

450 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (450000.00 行/秒)

サンプル2

AdventureWorksLT2012 のAddressテーブルに対するSELECTクエリの結果をテキストファイルに書き出します。
DB：AdventureWorksLT2012 (Evaluation)
Table：Address

SELECTクエリ：

SELECT * FROM AdventureWorksLT2012.SalesLT.Address WHERE City = 'London'

Power Shell 実行内容

PS C:\Users\hoge> bcp " SELECT * FROM AdventureWorksLT2012.SalesLT.Address WHERE City = 'London' " queryout "c:\Temp\Address_query.txt" -S 192.168.1.50\SQLEVA -T -c -t","

コピーを開始しています...
14 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計 : 1 平均 : (14000.00 行/秒)

使用したオプション（大文字/小文字の区別がある）

コマンド	説明
queryout	出力するファイルのフルパス
-S	[ServerName][Instanse Name]
-T	Windows認証の場合に指定
-c	文字データとして処理する場合に指定
-t	列の区切りを指定

※SELECTクエリを使ってデータを抽出する場合は書き出すファイルのコマンドを「out」ではなく「queryout」を指定する。

出力結果
※先頭行に列名が入らないことに注意

まとめ

BCPコマンドを使ってSQL Server のデータをテキストファイルに出力するサンプルを作成してみました。
さらに細かい設定は「format」指定をしてXMLのフォーマットファイルに指定できる模様です。
SSMSの結果をコピペでEXCELに貼ることしかやったことありませんでしたので勉強になりました…
使うか？と言われると微妙。

参考

bcp ユーティリティ

bcp ユーティリティを使用した一括データのインポートとエクスポート (SQL Server)

【SQL Server】bcpを使用したインポートとエクスポート

SSISプロジェクトを統合サービスカタログへ配置

前回の投稿 でSSISプロジェクトの作成方法をまとめました。
今回は作成したSSISプロジェクトをSQL Serverの統合カタログに登録しSQL Serverから実行してみたいと思います。

環境

• 開発環境：VS2015 Community Update 3
• SQL Server Data Tools：SSDT for VS2015 (14.0.61712.50)
  → SSIS用のプロジェクトを作成するためにはVisual Studioの導入後に対応するSSDTをインストールする必要があります。
  Visual Studio の SQL Server Data Tools (SSDT) をダウンロードし、インストールする
• データソース： SQL Server 2012 SP4 (11.0.7001.0 (X64)) Evaluation
  　　　　　　　 AdventureWorksLT2012

　　　※SQL Server はEvaluation又はStandard以上のライセンスが必要です!!

• データ保存先：ローカル ファイル（テキスト ファイル）

統合サービスカタログの作成

SSISのプロジェクトは「統合サービスカタログ」に配置することが出来る。
まずは、統合サービスカタログの初期設定を行う。

• SSMSを起動し該当のDBインスタンスで接続する。（192.168.1.50￥SQLEVA）
  　※Windows認証でSSMSにログインすること。
• SSMSのオブジェクトエクスプローラーから「統合サービス カタログ」を右クリックし「カタログの作成」を選択する。
  フォルダ名を「AdventureWorksLT2012」と設定する。

カタログの作成を行うとオブジェクトエクスプローラーの統合サービスカタログの下に「SSISDB」が作られその下に作成したカタログ名のディレクトリが作成される。

SSISプロジェクトの配置

前回の投稿 で作成したプロジェクトをSQL Server の統合サービスカタログに配置する。
プロジェクトはVisual Studio 2015 で作成しており、SSDTは14.0.61712.50を使用している。この環境設定の場合は既定でSQL Server 2017 に対応している。
SSISプロジェクトを統合サービスカタログに配置するためには、SSISのPackageFormatVersion等のConfig設定が対象のSQL Serverに適合していなければならない。
※配置するまではエラー無く出来るが、SSMSからパッケージを実行するとエラーが発生する。
※手動でConfigを変更するのは難儀…
Visual Studio 2015以降であれば、SSISプロジェクトのプロパティの設定で配置対象のSQL Serverを変更することが出来る。


1. 配置プロジェクトのターゲットバージョンを設定
・プロジェクトのプロパティを選択



・ターゲットバージョンを変更
構成プロパティ→全般から設定出来る。
既定でSQL Server 2017 になっているのを SQL Server 2012 に変更する。




2. パッケージの配置
・プロジェクトメニューから「配置」を選択する。
　→「Integration Services 配置ウィザード」が起動する




・「Integration Services 配置ウィザード」が起動




・配置先を選択
サーバー名と認証を設定し「接続」を選択
※サーバー名：192.168.1.50\SQLEVA
※認証はWindows認証を選択すること




・パスを設定するため、「参照」を選択




・統合サービスカタログの選択
「AdventureWorksLT2012」を選択しOKする。




・設定された配置先情報
入力内容に問題なければ「次へ」




・配置内容の確認
問題が無ければ「配置」を実行




・配置結果



※SQL Server認証で実施すると最後の「プロジェクトを配置しています」でエラーが発生する。
※リモート接続でSQL Server認証にしたからかも…




・配置結果の確認
SSISプロジェクトの配置処理が正常に終了したら、SSMSの統合サービスカタログを確認すると、配置したプロジェクトのディレクトリが作成される。





3. パッケージの実行
Step 2 で配置したSSISプロジェクトのパッケージを実行する。
・SSMSの統合サービスカタログの該当するプロジェクトのディレクトリを展開して「パッケージ」内の「Package.dtsx」を右クリックし「実行」を選択




・パラメータの値を設定
パラメータの値が設定されていない警告が出ているため左のボタンから値を設定する。










パラメータ値の設定をしたら「OK」する。


・実行結果レポートを表示するため「はい」を選択





・実行結果レポート（概要）




・出力データの確認





パッケージの実行エラー
SSISプロジェクトの配置ターゲットバージョンを適切に設定しないと以下のエラーが実行時に発生する。
※配置時にエラーが発生しないので注意!!
・実行結果レポート（エラー発生時）
実行情報の「状態」が「失敗」となる。




・エラー内容例

Package:エラー: エラー 0xC001700A
“パッケージに含まれるバージョン番号が無効です。バージョン番号を現在のバージョン番号より大きくすることはできません。”
により、パッケージをバージョン 8 から 6 に移行できませんでした。

上記エラーはSSISプロジェクトの配置ターゲットバージョンが配置するSQL Serverに対応していないことを示している。
※バージョン 8　：　SQL Server 2014 - 2017
※バージョン 6　：　SQL Server 2012

参考

What SQL version is my SSIS package? | SQL Studies

SSIS The version number in the package is not valid ? SQLGardner

まとめ

SSISプロジェクトを実装するためのVisual Studio のVersion（正確にはSSDTのVersion） とSSISプロジェクトのターゲットバージョンが関連していることに気が付かず3日くらい「バージョン番号問題」に苦しみました…
それまでVisual Studio 2013で作成しており、ターゲットバージョンの変更が出来ずにConfigの手動書き換えもやってみましたが動かず…
Visual Studio 2015 を導入しプロジェクトのプロパティでターゲットバージョンの変更が出来るようになり解決。
実際にSSISプロジェクトを開発しパッケージを運用する場合は開発環境、運用環境の各Versionに十分に注意する必要がありそうです。