MSSQL Server to SQL Server Migration Using SSIS – Step-by-Step Guide

SQL Server to SQL Server Migration Using SSIS – Step-by-Step Guide

Introduction

Data migration is a common requirement during:

• SQL Server upgrades

• Application modernization

• Data center migration

• Environment refresh (Dev → QA → Production)

• Database consolidation projects

In this guide, we'll migrate data from one SQL Server instance to another using SQL Server Integration Services (SSIS) and Microsoft Visual Studio.

---

Migration Architecture

Source SQL Server
       |
   SSIS Package
       |
Target SQL Server

The package will:

1. Connect to source database

2. Read data from source tables

3. Perform any required transformations

4. Load data into target database

5. Validate row counts

6. Log migration results

---

Prerequisites

Before starting, ensure:

Source Environment

• SQL Server source database access

• Read permissions

Target Environment

• SQL Server target database access

• Create/Insert permissions

Development Machine

Install:

• Microsoft Visual Studio 2022

• SSIS Extension for Visual Studio

• SQL Server Management Studio (SSMS)

---

Step 1 – Create the Target Database

Create the destination database.

CREATE DATABASE MigrationDB;
GO

Create required tables.

Example:

CREATE TABLE dbo.Customer
(
    CustomerID INT,
    CustomerName NVARCHAR(100),
    EmailAddress NVARCHAR(200),
    RegistrationDate DATETIME2,
    AnnualIncome DECIMAL(12,2),
    City VARCHAR(100)
);

---

Step 2 – Create a New SSIS Project

Open Visual Studio.

Navigate

File
 → New
 → Project
 → Integration Services Project

Provide:

Project Name:
SQLServerMigration

Click Create.

---

Step 3 – Configure Connection Managers

At the bottom of the SSIS package:

Source Connection

New OLE DB Connection

Configure:

Server Name : Source SQL Server
Authentication : Windows/SQL Login
Database : SourceDB

Test Connection.

---

Target Connection

Create another OLE DB connection.

Server Name : Target SQL Server
Database : MigrationDB

Test Connection.

---

Step 4 – Add Data Flow Task

From SSIS Toolbox:

Control Flow
 → Data Flow Task

Rename:

Customer Migration

---

Step 5 – Configure Source

Double-click Data Flow.

Add:

OLE DB Source

Configure:

Connection Manager:
Source SQL Server

Choose:

SELECT *
FROM dbo.Customer

Preview data. (IF preview Data/table not available) then proceed with fill the initial catalog (might be missing before))

Verify records are visible.

---

Step 6 – Add Data Conversion (Optional)

If source and target datatypes differ:

Add:

Data Conversion Transformation

Example:

VARCHAR → NVARCHAR
INT → BIGINT
DATETIME → DATETIME2

In this example, the source database stores CustomerName and EmailAddress as VARCHAR. 
The target database requires Unicode support using NVARCHAR. To handle this, 
a Data Conversion Transformation is added in SSIS to 
convert DT_STR (non-Unicode string) to DT_WSTR (Unicode string) 
before loading the data into the target table.

This step avoids common migration failures.

---

Step 7 – Configure Destination

Add:

OLE DB Destination

Connect Source → Destination.

Configure:

Connection:
Target SQL Server

Select:

dbo.Customer

Map columns.

Example:

---

Step 8 – Handle Errors

Production migrations should never ignore bad records.

Add:

Error Output

Redirect rows to:

Flat File Destination

Example file:

error.csv

Capture:

• Data conversion failures

• Truncation issues

• Null violations

---

Step 9 – Enable Logging

In Control Flow:

SSIS
 → Logging

Enable:

OnError
OnTaskFailed
OnWarning
OnInformation

Store logs in:

SQL Table

or

Text File

---

Step 10 – Validate Data

After execution compare counts.

Source:

SELECT COUNT(*)
FROM dbo.Customer;

Target:

SELECT COUNT(*)
FROM dbo.Customer;

Verify:

Source Count = Target Count

---

Step 11 – Execute Package

Click:

Start

or

F5

Expected Result:

Green Check Mark

indicates successful migration.

---

Step 12 – Deploy Package

Build Project:

Build
 → Build Solution

Deployment file generated:

ProjectName.ispac
SQLServerMigration -> 
D:\SSiS_project\Integration Services Project3\bin\Development\SQLServerMigration.ispac

Deploy to:

SSIS Catalog (Recommended)

In SSMS:

Integration Services Catalogs
 → SSISDB

Right-click:

Deploy Project

Select:

ProjectName.ispac

Deploy.

---

Production Best Practices

Use Batch Loading

Instead of:

SELECT *

Use incremental loads.

Example:

WHERE ModifiedDate >= ?

---

Disable Indexes During Large Loads

Large migrations become faster.

Rebuild indexes after migration.

---

Validate Data

Check:

• Row counts

• Duplicate records

• Null values

• Referential integrity

---

Use Environment Variables

Avoid hardcoded:

• Server names

• Database names

• Credentials

Use SSIS Parameters.

---

Common Errors and Fixes

Error	Cause	Solution
Login Failed	Authentication issue	Verify credentials
Truncation Error	Column size mismatch	Increase target size
Conversion Failed	Datatype mismatch	Use Data Conversion
Timeout	Large data volume	Increase timeout
Duplicate Key	Existing data	Use staging table

---

Conclusion

SSIS provides a reliable and scalable approach for SQL Server-to-SQL Server migrations. By using Data Flow Tasks, error handling, logging, validation, and deployment through SSISDB, organizations can execute migrations with greater control, auditability, and repeatability.