SSIS synchronizes multiple databases

This week received a new demand from IBM DB2, synchronizing data to SQL Server. In from SQL Server, synchronize to Oracle.

Because IBM is a 32-bit platform, Oracle is a 64-bit platform. and requires the use of scheduled tasks, so there are two copies of this stuff.

Installation

Installation is the beginning of everything, originally very simple things, but also waste a little time.

First SSIS, full name: Microsoft SQL Server Integration Services. Therefore, you need to install the Integration Services service on SQL Server.

After you install this service, you also need to install the client. Sql2008 above, included in the database tool.

Create

Tools for developing SSIS are called: SQL Server Business Intelligence Development Studio. Similar to the 2005 vs icon.

When open, create a new project. Select Integration Services.

You can then drag the control to the panel from the Toolbox on the left.

When you need to connect to a database, you can create a data source in the upper-right corner. And right-click from the connection Manager to create a new connection from the data source.

In the Properties panel in the package file, you can choose how sensitive information is saved.

If you want to save your link string password, etc., sensitive information. You can use a password to protect it.

In the options, ProtectionLevel, select Password, which can be password protected.

Then, when you save as a copy of the file, fill in the password.

Create a batch program that executes on a regular basis.

@echo off
CD C:\Program Files\Microsoft SQL Server\100\dts\binn
dtexec.exe/f "E:\CHENXY\SSIS\AS400TOMSSQL.DTSX"/de 123

SSIS synchronizes multiple databases