SQL Server 2016 で Business Intelligence Development Studio がない

 

嫁と娘は文化祭巡りに行ってしまい、ボッチになってしまったので、今日は久しぶりに引きこもってSQL Server 2016 ETLツールを触ってみたいと思います。

昔、Integration Services のパッケージデザインやAnalysis Services の多次元データベース設計で Business Intelligence Development Studio を使っていました。

最新版の SQL Server 2016 Enterprise版をインストールしてみると Business Intelligence Development Studio がありません。

SSDT（SQL Server Data Tools）という無償のツールで同様のデザインができるという感じっぽいので、自宅PCでSQL Server 2016 をインストールし、開発環境周りの確認をしてみます。

 

SQL Server 2016 を展開するためのWindows Server を用意

我が家の中で最もハイスペックなマシン（Windows 10パソコン Core i7 3770 3.40Mhz メモリ32GB）のHype-V上にWindows Server 2012 R2 Standard 評価版 を構築します。Windows 10 を展開してもいいのですが、実働環境に近い形でWindows Server にしました。

 

SQL Server 2016 評価版をダウンロード

下記よりセットアッププログラム（SQLServer2016-SSEI-Eval.exe）をダウンロードします。

https://www.microsoft.com/ja-jp/evalcenter/evaluate-sql-server-2016

ダウンロードした SQLServer2016-SSEI-Eval.exe を実行すると、インストールを開始するか、メディア（ISOかCabのいずれか）をダウンロードするか選択できます。

Hyper-Vの仮想マシンのDVDドライブにマウントするのでISO版をダウンロードします。

 

 

SQL Server 2016 インストール前のサーバの機能確認

デフォルトですが.NET 3.5は有効化されていなく、.NET 4.6はインストールされていません。SQL Server 2016、およびSQL Server Data Tools、Management Studioすべてをインストールした後でも、.NET 3.5は有効化されていませんでしたので、SQL Server 2016からは.NET3.5は不要になったのでしょうか・・・

 

 

SQL Server 2016 をインストール

仮想マシンへインストールします。まずはSQL Sever本体をインストールします。

今回の目標ではデザイン・開発ツールを確認するだけなので、最も機能が豊富な180日間限定のEvaluationエディションを選択しました。

更新プログラムでデザイン・開発ツールが自動で入ってしまうと切り分けできないので外します。

クリティカルなセキュリティ更新だけは入れます。

2016 MSVCRT の SQL Server の必須コンポーネントの重要な更新プログラム
https://support.microsoft.com/ja-jp/kb/3164398

インストールするサービスはデータべースサービスエンジン、Analysis Services、Reporting Services、Integration Servicesを選択します。

ドメイン環境でもないので、各サービスアカウントはデフォルトにしました。

照合順序もデフォルトのJapanese_CI_AS。

データベースエンジンとAnalysis Servicesの構成定義。

Business Intelligence Development Studio が見当たりません。

SQL Server インストール完了。やはり Business Intelligence Development Studio のようなものは見当たりません。

 

 

Management Studioをインストール

インストールセンターからSQL Server Management Toolsをクリックするとダウロードページに飛ぶのでセットアップファイルを入手しインストールします。

SQL Server Management Studio (SSMS) のダウンロード
https://msdn.microsoft.com/ja-jp/library/mt238290.aspx

Management Studio 以外にもいろいろ追加されました。

 

SQL Server Data Tools (SSDT) をインストール

 

SQL Server Data Tools (SSDT) のダウンロード
https://msdn.microsoft.com/ja-JP/library/mt204009.aspx

SQL Server Data Tools 2015 と Visual Studio 2015 がインストールされました。

SQL Server Data Tools 2015 を起動してみます。

Visual Studio ベースの開発ツールが起動しました。Business Intelligence Development Studio の後継が SQL Server Data Tools (SSDT)  ということでいいのでしょうか・・・まあ、どっちらも Visual Studio ベースですし。

Integration Services のプロジェクトも作成、編集できそうです。

Analysis Services のプロジェクトもOK。

Analysis Services のデータベース（Cube）も開けるようです。

Reporting Services プロジェクトも対応しています。

Business Intelligence Development Studio の後継が SQL Server Data Tools (SSDT)  で良さそうです。

 

調べ方がよくなかったのか、Business Intelligence Development Studio の後継が SQL Server Data Tools (SSDT)であるという確信を得られなかったので、自分の家で入れてみようと思ったわけです。

SQL Server Data Tools (以下、SSDT）が無償提供のようなので迷いました。

そこで、SSDTは昔使っていたBusiness Intelligence Development Studio と同じようなことができるのか？確認してみたいと思います。

今回はSSISのプロジェクトからDTSXを作ることで確認します。レポートや多次元データベースのAnalysis Serviceプロジェクトは次回（次回があれば）以降にします。

 

（追記）

この記事のタイトル「SQL Server 2016 で Business Intelligence Development Studio がない」でGoogle先生に問い合わせしたら、以下のようにアナウンスされていました。SSDTはBusiness Intelligence Development Studioの後継であり、SQL Server のインストールメディアには含まれないということです。

SQL Server Data Tools の入手と注意点 – Microsoft SQL Server Japan Support Team Blog
https://blogs.msdn.microsoft.com/jpsql/2015/12/03/sql-server-data-tools/

SQL Server Reporting Services のレポートや、Integration Services パッケージ、Analysis Services のデータベース等を作成するにあたって、Business Intelligence Development Studio（SQL Server 2008 R2 までのツール） や SQL Server Data Tools（SQL Server 2012 以降のツール）を使用します。

SQL Server 2012 までは、SQL Server のインストーラーから、Business Intelligence Development Studio や SQL Server Data Tools をインストールすることができました。

SQL Server 2014 より、SQL Server Data Tools は SQL Server のインストーラーに含まれず、個別にダウンロードする必要があります。

ここでは、SQL Server Data Tools の入手先や注意点についてご案内します。

 

Integration Services を使ってみる

（ここでやること）

TMDbAPIで取得した映画情報をテキストファイルに保存したタブ区切りテキスト「TMDb.txt」をSSISを使ってクレンジングし、インポートします。

 

インポート先のDBとテーブルを作成

DB「MOVIE」を作成

TMDbMOVIEというテーブルを作ります。

USE [MOVIE]
GO

/****** Object:  Table [dbo].[TMDbMovie]    Script Date: 2016/09/10 15:19:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TMDbMovie](
	[TMDbID] [int] NOT NULL,
	[title] [nvarchar](200) NULL,
	[original_title] [nvarchar](200) NULL,
	[budget] [bigint] NULL,
	[revenue] [bigint] NULL,
	[homepage] [nvarchar](256) NULL,
	[IMDbID] [nvarchar](15) NULL,
	[overview] [nvarchar](max) NULL,
	[popularity] [numeric](5, 2) NULL,
	[release_date] [date] NULL,
	[runtime] [smallint] NULL,
	[tagline] [nvarchar](200) NULL,
	[vote_average] [numeric](6, 2) NULL,
	[vote_count] [int] NULL,
)
GO

テーブル「TMDbMOVIE」を作りました。

このTMDbMOVIEにタブ区切りテキストをIntegration Services（以降、SSIS）のDTSXパッケージでインポートします。

テキストファイルは以下のような感じです。1行目はタイトル行です。ちなみに1行目はスター・ウォーズです。

インポートされるとこんな感じになるはずです。ただし、以下の図ではbudget（予算）とrevenue（興業収入）はドルのままです。

 

SSDTでテキストデータをインポートする

SSDTで新規SSISパッケージを作成します。

上記のように「ソリューションのディレクトリを作成」にチェックすると、下図のようなフォルダ構成になり、ソリューションファイル（.sln）やプロジェクトファイル（.dtproj）が作成されます。

SSISのプロジェクト編集画面です。

処理フローは下記のとおりです。

1. テーブル TMDbMOVIE をTRUNCATE（テーブル初期化）
2. テキストファイル TMDb.txt の予算と興業収入を日本円（1ドル102円として）に変換
3. 2をテーブル TMDbMOVIE にインポートする。

各ステップごとにデバッグします。

 

1　テーブル TMDbMOVIE をTRUNCATE（テーブル初期化）

 

テーブル TMDbMOVIE へ接続するための接続マネージャの作成

まず、 TMDbMOVIE をTRUNCATE するために、TMDbMOVIE への接続を作成します。

OLE DB接続ができました。

TRUNCATE処理を記述

ツールボックスから SQL実行タスクを選んで、制御フローへドラッグアンドドロップします。SQL実行タスクの内容が未定義で実行できる状況にないので、Xがついています。

SQL実行タスクをダブルクリックして、Connecion（接続マネージャ）を選び、TRUNCATE文を直接記述します。

SQL実行タスクの内容（実行すべき内容）を記述したのでXが消えました。上にある「▶開始」を押してデバックします。デバックといっても実際に処理されます。

正常に処理（TRUNCATE）されました。デバックを中断します。

 

2　テキストファイル TMDb.txt の予算と興業収入を日本円（1ドル102円として）に変換

 

フラットファイル（テキストファイル TMDb.txt のこと）に接続するための情報作成

 

テキストファイルを指定します。テキストファイルの属性（区切り文字など）を指定します。

列を確認します。ここでは列の区切りを確認します。列属性（文字とか数値とか）はこの後です。

各列の属性を指定します。

各列の定義が終了したらプレビューし、OKします。

フラットファイル（テキストファイル TMDb.txt のこと）に接続するための情報が作成されました。

 

インポート処理の作成

ツールボックスから「データフロータスク」を制御フローへドラッグアンドドロップします。

TRUNCATEするSQL実行タスクから伸びる矢印をデータフロータスクへ接続します。接続したらデータフロータスクをダブルクリックします。

制御フロータブからデータフロータブへ移動するので、ツールボックスからフラットファイルソースを探します。

フラットファイルソースをデータフローへドラッグアンドドロップします。

フラットファイルソースをダブルクリックして内容を定義します。

不要な列があればここで切り捨てます。今回はすべて取り込みます。OKを押します。

ドルの予算額や興業収入額を円に変換するために、派生列をドラッグアンドドロップします。

派生列をダブルクリックし、派生列を定義（ドル円変換）します。

1ドルを102円として円に変換し、新しい列として作成します。置換もできますが、ここでは新しい列を作成し円を入れます。

デバックしてみます。エラーとなりました。

データクレンジングではよくあることですね。エラー原因を探ります。

出力にエラーメッセージが表示されています。

エラー: 0xC02020A1 (データ フロー タスク, フラット ファイル ソース [2]: データ変換に失敗しました。列 "TMDbID" のデータ変換から、状態値 2 と状態を示すテキスト "データが失われる可能性があるため、値を変換できませんでした。" が返されました。

列 "TMDbID" がおかしいということです。エラーデータをログファイルへ出力（リダイレクト）するようにします。ログファイルはテキストファイルとするので、フラットファイル接続とフラットファイルを配置します。図のようにエラーの矢印（赤）をエラー出力用のフラットファイルソースに接続します。

列 "TMDbID"に対し、エラーと切り捨てが発生したらその行をリダイレクトするように定義しました。

それでもまたエラーとなりました。

エラー: 0xC02020A1 (データ フロー タスク, フラット ファイル ソース [2]: データ変換に失敗しました。列 "revenue" のデータ変換から、状態値 2 と状態を示すテキスト "データが失われる可能性があるため、値を変換できませんでした。" が返されました。

列 "revenue"もおかしいということなので、エラー発生時はリダイレクトします。

なお、より見やすく処理内容を確認する場合は「実行結果タブ」に移ります。

まだエラーが発生しています。

フラット ファイル ソース [2]] エラー: データ変換に失敗しました。列 "release_date" のデータ変換から、状態値 2 と状態を示すテキスト "データが失われる可能性があるため、値を変換できませんでした。" が返されました。

面倒なので、すべての列に対し、リダイレクトするようにします。

今度はエラー無く処理されました。エラーは2行、正常終了は997行です。

エラーとなった2行はTMDb_error.logに出力されました。

TMDb_error.logの中身です。列がずれています。どこかに改行コードが入っていて、列がずれたのでしょう。

 

3　2をテーブル TMDbMOVIE にインポートする。

次にテーブル TMDbMOVIE へインポートします。

インポートにはOLE DB変換先を利用します。ツールボックスからOLE DB変換先をドラッグアンドドロップします。

派生列から矢印をつなぎます。

OLE DBをダブルクリックします。

派生列で変換したテキストデータの列とテーブルTMDbMOVIEの列が自動で対応されます。

興業収入派生列で新規に作成した列、日本円の予算額、興業収入額は取り残されています。

列名で自動対応しているので、ドルの列は対応を外します。

代わりに円の列を対応付けます。OKを押します。

テキストデータのtagline（キャッチフレーズ）2000文字に対し、インポート先のtaglineが200バイトなので切り捨てが起きると警告されています。

フラットファイルの列 tagline の文字数を200文字に変更します。

派生列の上流にあるフラットファイルソースの属性を変更したので、同期がとれていないと警告がでます。もう一度フラットファイルソースをダブルクリックして属性を開き、OKを押して閉じると、下流側の派生列やOLE DB変換先の属性が自動で変換されます。

同期がとれ、警告も消えました。

 

インポート処理の実行

997件インポートできました。

インポート前

インポート後

テキストファイルがインポートされました。

フラットファイルのクレンジングからインポートまで簡易的に確認しました。SSDTは、SSISパッケージ作成において Business Intelligence Development Studio と呼ばれていた頃のものよりも使いやすく、コンポーネントも増えているような気がします。

ということで結論ですが、SQL Server 2016 をインストールしただけでは、SSIS、SSAS、SSRS用の開発ツールはインストールされないし、インストールメディアにも収録されていないので、別途マイクロソフトのサイトから入手し、インストールする必要があるようです。

デバッグまで終わっているので、DTEXECコマンド等で実行してみます。

 

 

DTSXパッケージの実行

これまではVisual Studio上でデバッグ実行していました。いくつかの方法でDTSXパッケージを実行してみようと思います。３つの方法があるようです。

 

１　DTSXファイルのパスをSSISサービスに登録する

Management Studio から Integration Services に接続します。

パッケージが格納される（どこかは知らない）のでそれを実行します。

64bitモードで実行されたかはわかりません。

 

2　DTEXECコマンドで実行

32bit版で実行されてしまいました。

DTEXEC /F D:\SSIS\SSIS_MOVIE\SSIS_MOVIE\obj\Development\Package.dtsx > c:\users\administrator\desktop\dtexec.log

64bit版のDTEXECを指定しないと32bitで動作してしまいます。とりあえず無理やり64bitのDTEXECを指定しましたが、適切な方法があると思います。

"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\"DTEXEC /F D:\SSIS\SSIS_MOVIE\SSIS_MOVIE\obj\Development\Package.dtsx > c:\users\administrator\desktop\dtexec.log

 

3　SSISカタログに配置

結局この手順はうまくいきませんでした。最後のパッケージをSSISDBに配置するところで失敗します。

一応やったことのメモとして残しておきます。

開発サーバで作成したパッケージを本番サーバのSQL ServerのSSISDBに登録するようなケースではこの手順でしょうか・・・

 

SSIDDBを作成

配置先となるSSISDBを作成します。手順は下記を参考のとおりです。

SSIS カタログの作成
https://msdn.microsoft.com/ja-jp/library/gg471509.aspx

 

Management Studioからデータベースエンジンに接続します。

パスワードは単純じゃダメみたいです。

 

配置オブジェクトを作成

プロジェクトをビルドして配置オブジェクトを作成します。

更新をクリックします。パスワードは不要です。

配置先のサーバ名を入れます。参照をクリックします。

先ほど作成したSSIDDBが表示されます。まずフォルダを作ります。

新しいプロジェクトをクリックします。

コマンド ライン: /Silent /ModelType:Package /SourcePath:"D:\SSIS\SSIS_MOVIE\SSIS_MOVIE\obj\Development" /Packages:"Package.dtsx;" /DestinationServer:"vSVBI" /DestinationPath:"/SSISDB/MOVIE/MOVIE"

 

配置をクリックすると以下のように失敗。ミスをしたか根本的に何かが足りないようです。

プロジェクトをパッケージ配置モデルにしたり、いろいろやってダメなのでひとまず諦めます。