Home > SSIS > An instant data migration package – SSIS

An instant data migration package – SSIS


In this blog post, I am going to show you how to create an instant SSIS package for data migration project.

Scenario:

I have a requirement to transfer data from multiple tables in server A to Server B daily. But, I am lazy to create a SSIS package from the scratch. So, I am going to use Import and Export wizard to create a package.

Creating instant SSIS package.

Implementation

Step 1: Open Import/Export wizard

pic1Step 2: Connect to source server and select source database.

pic2Step 3: Select destination server and server database to migrate data

pic3Step 4: Select data migration method in this step. I am going to use table copy option

pic4Step 5: Select tables from the list for migration. I just picked two tables for this demo.

pic5Step 6: This is an important step. In this step we are going to save all the settings to SSIS package.

There are two option for saving the package.

  1. Save to SQL Server
  2. Save to file system

We want to save this package into dtsx file. So, we can open in BIDS and alter the package if we need in future.

I have also selected Package protection level as Do not save sensitive data. So, this package will not keep the sensitive information such as password in the dtsx file.

pic6Step 7: Give package name and location for saving SSIS package file in this step

pic7Step 8:  This is the final step in this wizard. In this step we have to verify all the settings and click finish to save the DTXS

pic1You should see the below image on your screen. It is showing the details of the package execution.

pic2Now, we have got a SSIS package with all the above steps ready for execution. So, I am going to open this package and execute once again.

DataMigration package Control Flow

pic3It has a Execute SQL Task and a Data Flow Task. We need only Data Flow task for data migration. Because, Execute SQL Task contains table creation script for the initial schema migration. But, we have created these table in the destination server already. So, I am going to delete the execute SQL task from this package.

DataMigration package Data Flow Task

pic4

Executing DataMigration package in BIDS.

pic4Now, we can add any business logic inside this package any time to enhance this package for our future need.

This is the simplest way to create a SSIS package. Do you like this?

As always, share your thoughts in the comment section.

Advertisements
  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: