Data Migration using SSIS

An Overview On SSIS

Let’s take a scenario where same database task has to be performed multiple times or Transfer data from one server to other. Can these process be automated with visual empowerment? Well this can be achieved with the help of SSIS. SSIS (Sql Server Integration Services), a component of the Microsoft SQL Server database software is a tool that integrates, transforms and migrates enterprise data.


It is an ETL tool (Extract, Transform and Load) which Gets the Data from various sources (like excel, text files, databases), performs different transformations operations (like aggregation, conditional splitting, merging), defines a workflow of the process and loads the data to the destination based on the need. SSIS is more of a visual part which uses text, components and flow charts to define the workflow process.

SSIS files are organized into packages, projects and solutions. The package is at the bottom of the hierarchy and contains the tasks necessary to perform the actual extract, transform, and load (ETL) operations. We can include one or more packages in a project. That project in turn is part of a solution, which is at the top of the hierarchy.

Packages are constructed by using different elements available in SSIS toolbox which can be dragged and dropped.

Package are composed of below components:

  • Control flow
  • Data flow
  • Connection Managers

The control flow defines the flow of package or the workflow and is constructed by using different type of control flow elements: the containers that provide structure in packages and services to tasks, tasks that provide functionality in packages, and precedence constraints that connect containers and tasks into a control flow.

Data Flow is a subset of Control Flow which is for the ETL Process. It encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. A Data Flow task can include multiple data flows. If a task copies several sets of data, and if the order in which the data is copied is not significant, it can be more convenient to include multiple data flows in the Data Flow.

Connection managers are logical representation of a connection. It describes a physical connection to source and destination data stores like XML, Excel or relational databases to extract and load data.

flow diagram

Implementation of SSIS for Data Migration:

Let’s look at how to use Visual Studio to load SSIS and various options involved and how to customize settings within Visual Studio to make it easy to use.

In order to create and maintain an SSIS Project in Visual Studio SQL Server Data Tools (SSDT) is required which can be downloaded for free (Download Link: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt). It allows users to edit SSIS packages using a drag-and-drop user interface. A scripting environment in which to write programming code is also available in the tool.


Steps for Data Migration Process:

  1. Open Visual Studio on the File menu, point to New, and click Project to create a new Integration Services project.
  2. In the New Project dialog box, expand the Business Intelligence node under Installed Templates, and select Integration Services Project in the Templates pane.
  3. In the Name box, change the default name. Optionally, clear the Create directory for solution check box.
  4. In Solution Explorer toolbar, rename the empty Package created by default.
  5. Right click on Connection Managers and add the connection for Source and Destination.
  6. Drag and drop the Control Flow and Data Flow elements from SSIS ToolBox as per the requirements.
  7. After designing Right Click on Package and select Execute Package which executes the Package as a whole by logging the results in Progress Tab and Execution Result Tab which
    Visual Studio IDE

    is shown after executing the Package.

Challenges Faced:

Incurred Assertion Issue while Migrating Files which are stored in Database as File Streams.


[ERROR] Error: A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

Cannot continue the execution because the session is in the kill state.

A severe error occurred on the current command.  The results, if any, should be discarded.

Location:               “xact.cpp”:4322

Expression:          !m_updNestedXactCnt

SPID:                      52

Process ID:           4048

Description:         Trying to use the transaction while there are 1 parallel nested xacts outstanding


System assertion error will raise when there are threads or process are in sleeping mode and transaction is trying to commit. This is a SQL Server bug.


SQL Server creates the threads for better performance and those will be committed by SQL server.  This is a default server setting.

Resolution 1: This can be fixed by checking and killing those idle threads or processes manually.

Resolution 2: By using MAXDOP – Maximum Degree of Parallelism. On setting the MAXDOP 1 in the query hint, we are suppressing the Degree of Parallelism (Default Setting) and, forcing the SQL server to not create parallel threads.






About The Author

Leave a Reply