Home > Database > Database Migration using Data Transformation Services

Database Migration using Data Transformation Services

More or less, 2 months ago, while i’m in a meeting with my client, i got a bad news from them, they said that i have to move current system’s data in MySQL to our new system that used Sql Server as its Database Engine. For maybe 1 or 2 hours i’m in a totally panic condition. In my cloudy imagination, i have to develop a program that read MySQL data, translate them to our structure, and then insert the data into Sql Server… The Program is simple, easy to develop, but my biggest awareness is, how many hours that i have to use to migrate all of the data?

Well luckily, i’m not stop at the line. After my meeting finished, i googled about it, and found some links that are very useful (sorry not to post the links, i lost them all..seriously)…. all of the links said that i can used Data Transformation Services (DTS) to migrate from other databases to Sql Server. Thanx God, after i read those statements,  i can be more relax at that moment. Maybe some of you already know about DTS, and can explain it better than me, if so please feel free to give your own explanation in the comment section, or just drop your post in my email  rd.djunaedi [at]Gmail[dot][com]… so i can have this blog more in quality… oh, anyway, if you are using SQL Server Express, you have to download the DTS from http://go.microsoft.com/fwlink/?LinkId=65111, since it’s came with no DTS inside.

A brief description of DTS, well, i got this from Wikipedia.

Data Transformation Services, or DTS, is a set of objects and utilities to allow the automation of extract, transform and load operations to or from a database. The objects are DTS packages and their components, and the utilities are called DTS tools. DTS was included with earlier versions of Microsoft SQL Server, and was almost always used with SQL Server databases, although it could be used independently with other databases.

DTS allows data to be transformed and loaded from heterogeneous sources using OLE DBODBC, or text-only files, into any supported database. DTS can also allow automation of data import or transformation on a scheduled basis, and can perform additional functions such as FTPing files and executing external programs. In addition, DTS provides an alternative method of version control and backup for packages when used in conjunction with a version control system, such as Microsoft Visual SourceSafe .

In a short term, here are what you have to do to use DTS.

  1. Dealing with data source, both for your source and destination database.
  2. Set up the DTS.
  3. Choose your table or even data to be migrate.
  4. Execute it to run your Data Migration.
Advertisements

Pages: 1 2 3 4 5

Categories: Database
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: