PostgreSQLのデータをSQL Server に引っ越しさせる (SSIS版)

細かいところは、さておき、大まかな手順を。
ここでは、スケジューリング化出来る、SQL ServerのIntegration Service (SSIS) を使う例を。
SQL Serverに詳しくない方もいらっしゃると思いますので、ポイントは比較的丁寧に記載します。

概要

ここでは、以下のステップで、移行をさせます。ストアドプロシージャーは機械的な移行は大変難しいので、ここでは対象外とします。

PostgreSQL == (SSIS) è SQL Server (On Premise)
Azureへも。SQL Server からè (Windows Azureの) SQL database へ。これはたくさんツールがありますので。

 

注意点

  • SQL Serverには配列の概念がありません。
  • ODBC Driver。Visual Studioを含む多くのアプリは32bit。例えDBとOSが64bitでも。
  • SQL Server 2012 SP1のCU3には、Integration Servicesのプロジェクトを作成できない問題があり。以下のホットフィックスでそれは修正されます。

 

事前作業

 

作業手順

PostgreSQL DBは以下の様になっています。
pgAdmin III もGUIでDB管理が出来て、大変良いツールですね。

以下にDDL文を。DBとSchemaはデフォルトのままです。

 

CREATE DATABASE postgres

WITH OWNER = postgres

ENCODING = ‘UTF8’

TABLESPACE = pg_default

LC_COLLATE = ‘Japanese_Japan.932’

LC_CTYPE = ‘Japanese_Japan.932’

CONNECTION LIMIT = -1;

 

CREATE SCHEMA public

AUTHORIZATION postgres;

 

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO public;

COMMENT ON SCHEMA public

IS ‘standard public schema’;

 

— Table: table01

 

— DROP TABLE table01;

 

CREATE TABLE table01

(

col1 integer NOT NULL,

col2 date,

col3 character(1000),

CONSTRAINT primarykey01 PRIMARY KEY (col1)

)

WITH (

OIDS=FALSE

);

ALTER TABLE table01

OWNER TO postgres;

 

— View: view01

 

— DROP VIEW view01;

 

CREATE OR REPLACE VIEW view01 AS

SELECT table01.col1, table01.col2, table01.col3

FROM table01

WHERE date_part(‘year’::text, table01.col2) = date_part(‘year’::text, ‘now’::text::date);

 

ALTER TABLE view01

OWNER TO postgres;

 

要は、テーブルが1つと、ビューが1つですね(笑)。
ビューのSQL文はテーブルスキャンを必ずしますので、本番では使わない事をお勧めします・・・

こちらがテーブルの内容です。

 

SQL Server Express内にDB作成

SSISでは、データベースそのものは作成してくれません。名前は何でもいいです。

SQL Server Management Studio から、行います。GUIからでも、SQL 文を発行しても良いです。

 

ここでは、以下の様に。
データのサイズは大きめにとってください。以下の画面ですと、[ファイルの種類] の[初期サイズ (MB)] を大きめにしてください。

 

また、一時テーブルであれば、ログファイルの管理は不要です。
SQL Serverでは、ログファイルへのデータベースのログ書き込みを「復旧モデル」にて定義します。バッチ用の作業DBでしたら、途中でエラーがあったら全部やり直しでもいいかと思いますので、[単純] にするのをおすすめします。

SQL Server 復旧モデル: http://msdn.microsoft.com/ja-jp/library/ms189275.aspx

 

SQL Server Data Toolsを起動

SSISのジョブは、SQL Server Data Toolsより作成をしていきます。
[ビジネス インテリジェンス] より [Integration Services プロジェクト] を選択します。

 

データフローの作成

制御フローの画面が表示されています。
制御フローとは、いわゆる条件分岐、ループなどのプログラミングにあたる部分を作成していくところになります。
データフローは、変換など、データ処理に特化した部分を作成していきます。

画面左の[SSIS ツールボックス]から[データ フロー タスク] をドラックアンドドロップにて、制御フロー内にもってきます。

PostgreSQLへの接続設定

画面一番下に、[接続マネージャー]があります。ここに、データソースであるPostgreSQLのインスタンスと、インポート先であるSQL Serverのインスタンスへの接続設定を作成します。

[新しい接続] から PostgreSQLのODBCドライバーを選択します。

 

ODBC を選択し、[追加]をクリック。

最初は空ですから、[新規作成]をクリックします。

[接続マネージャー] では[接続文字列を使用]を選択し、[ビルド]を選択します。

[データソース]を作成します。ここでは、[新規作成]を選択します。

 

ようやく、PostgreSQLのドライバーまでたどり着きました・・・

データソースの新規作成に適切な名前をつけまして。

完了です。

 

PostgreSQLへの接続文字列の設定が出てきますので、各項目にデータを入力します。
[OK]を押した後に、データソース 作成画面の [DSN名] のところがうまります。そこで[OK]を押すと、また、この画面にてパスワード入力を求められます。

 

これでようやくODBC Driverの接続設定が完了します。
以下の画面で[接続テスト]をして、PostgreSQLへの接続確認を行ってください。

 

接続できるようですと、以下の画面が表示されます。

これで、ようやく、ODBC設定ができます。

 

 

SQL Server への接続設定

同じく、接続マネージャーを右クリックして、SQL Serverへの接続の設定をします。
[新しいADO.NET接続] を選択します。

 

ADO.NET の接続マネージャーの構成 は空ですので [新規作成] を押します。

 

[接続マネージャー] から、サーバー名、ログイン設定を選択し、SQL Serverへの接続が出来ていると、データベースを選択できるようになります。

[OK]を押し、

 

元の制御タスクの画面に戻ります。

 

データコピーの設定

データフロータスクに画面を切り替えます。
画面左側の[SSIS ツールボックス]の項目がガラッと入れ替わります。

[SSISツールボックス]の[その他の変換元] から、[ODBC 入力元]を画面にドラックアンドドロップします。
赤いバツのマークは、設定が不足していることを示しています。

 

[ODBC変換元]を右クリックして、[編集]を選択します。これで、データベースの中の、どのオブジェクト(テーブルなど)からデータを持ってくるのかを指定します。

 

テーブルを指定します。

[プレビュー] ボタンを押すと、データの内容も確認できます。
ここで内容が見えると安心しますね。別のツールですからね。

 

次に、コピー先のSQL Serverを指定します。

[SSIS ツールボックス] から[ADO NET変換先]をドラックアンドドロップします。

 

“ODBC 変換先” を選択すると、青と、赤の矢印があるのが確認できます。青は正常終了時。赤はエラー発生時に、次にどこにつなぐかを設定します。青の矢印をドラックして、”ADO NET 変換先” に接続させます。

 

こんな形になります。

“ADO NET” 変換先にテーブルを作成します。”ADO NET 変換先”を右クリックして、[編集]を選択します。
ADO.NET 変換先エディターが表示されます。
ここでは、テーブルは存在していないですので [テーブルまたはビューを使用] の [新規作成] からテーブル作成のSQL文画面を呼び出します。

注意点です。

  • データ型はある程度自動的にマッピングしてくれます。
  • テーブル名は自動的に設定してくれませんので、修正ください。ここでは “table01” にする必要がありますね。
  • 主キーなどの制約系は作成してくれません。ここではPrimary Keyを追加する必要がありますね。

 

先の[ADO.NET 変換先エディター] の [マッピング] のところを選択すると、1:1での列マッピングの設定を見て取れます。

さ、これで一段落です。

32bitの実行環境を設定する。

このまま実行するとエラーになります。Visual Studioは32bit環境ですから、64bitのドライバーは動作しません。

以下、F5キーにてそのまま実行したもの。

実行環境を32bitに設定します。

画面右側の [ソリューション エクスプローラー] から、プロジェクトを右クリックします。その中から、[プロパティ]を選択します。

[構成 プロパティ] – [デバック] の [Run64BitRuntime]をFalseに設定します。

さ、これで、F5キーで、再度実行してみましょう。

 

データの確認

さ、SQL Server の中のデータを確認しましょう。
SQL Server Management Studio で移行先のデータベースを選択し、[最新の情報に更新] を押します。
新たにテーブルが作成されているのが分かると思います。

作成されたテーブルを右クリックし、[上位 1000行の選択] を押してください。

 

SELECT文が作成され、結果が表示されます。

 

SSISですが、一度作成してしまえば、別コンピュータで実行させる事も、指定した時間に実行させる事もできる非常に便利な機能になります。

DDL文の移行をさせるよりは、定期的なデータ同期での利用を是非ご検討ください。

http://www.microsoft.com/ja-jp/sqlserver/2012/technology/self-learning.aspx

広告

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中