SSIS Script Task to Copy Table Object with data from One SQL Server to Another using Transfer SQL Server Objects

Introduction

This tip focuses on copying database tables with its data from one MS SQL Server to another SQL server which is in a different environment. You can also copy other dependent objects, primary keys, foreign keys as well, but this post covers the basic concept on how to copy table objects using “Transfer SQL Server Objects Task“.

Background

When you want to move database tables from one SQL server to another and both SQL servers are in different environments with limited permission where creating link server is also not allowed, then we can think of the below options:

1. Create SSIS package for each table using DataFlow task and it will take care of creating table and moving data.
2. Use SSMS to import data with table from one server to another.

However, both options are good if you deal with 10 to 15 tables and if it is a onetime task. But what if this is a repetitive task, number of table objects are more, and Table list is updating time to time?

Using the Code

Note: The below code works with SQL Server 2015, 2016.

“Transfer SQL Server Objects Task” is a SSIS task used for moving data from one SQL server to another SQL server, however, in case you want to move the specific list of tables directly using this task control, it is a bit difficult. So attached here is the code for using “Transfer SQL Server Objects Task“ object in Script task control to handle the list of tables dynamically and copying it to another SQL Server.

The code has been written using Visual Basic.NET.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask
Imports System.Collections.Specialized
Imports Microsoft.SqlServer.Management.Smo.Server
Imports Microsoft.SqlServer.Management.Smo

Public Sub Main()
 
            Dim SourceServer As String = Dts.Variables("srcSMOServerName").Value.ToString
            Dim SourceDatabase As String = Dts.Variables("srcDatabaseName").Value.ToString
            Dim sourceUserName As String = Dts.Variables("srcUserName").Value.ToString
            Dim sourcePassword As String = Dts.Variables("srcPassword").Value.ToString

            Dim TargetServer As String = Dts.Variables("DestSMOServerName").Value.ToString
            Dim TargetDatabase As String = Dts.Variables("DestDatabaseName").Value.ToString
            Dim TargetUserName As String = Dts.Variables("DestUserName").Value.ToString
            Dim TargetPassword As String = Dts.Variables("DestPassword").Value.ToString

            Dim SourceConnection As String 
            Dim DestinationConnection As String 

            Dim dt As New DataTable
            Dim pkg As Package = New Package()
            Dim conns As Connections = pkg.Connections
            
             'create a stringcollection of tables
            Dim Tables As StringCollection = New StringCollection()
            Tables.Add(Dts.Variables("User::vTableName").Value.ToString())
            
            SourceConnection = "SqlServerName=" & SourceServer & ";UserName=" & _
            sourceUserName & ";Password=" & sourcePassword & ";UseWindowsAuthentication=False;"
            DestinationConnection = "SqlServerName=" & DestinationServer & ";UserName=" & _
            DestinationUserName & ";Password=" & _
            DestinationPassword & ";UseWindowsAuthentication=False;"            

            ' Add a ConnectionManager to the Connections collection.
            Dim cmSource As ConnectionManager = pkg.Connections.Add("SMOServer")
            cmSource.Name = "SMOSourceServer"
            cmSource.ConnectionString = SourceConnection
           
            Dim cmDestination As ConnectionManager = pkg.Connections.Add("SMOServer")
            cmDestination.Name = "SMODestinationServer"
            cmDestination.ConnectionString = DestinationConnection
           
            'create SQL Server object task to move tables
            Dim xfr As Executable = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask")
            Dim xfrTask As TaskHost = CType(xfr, TaskHost)

            ' Setting properties for TransferSqlServerObjectsTask control
            xfrTask.Properties("SourceConnection").SetValue(xfrTask, cmSource.Name)
            xfrTask.Properties("SourceDatabase").SetValue(xfrTask, SourceDatabase)
            xfrTask.Properties("DestinationConnection").SetValue(xfrTask, cmDestination.Name)
            xfrTask.Properties("DestinationDatabase").SetValue(xfrTask, DestinationDatabase)
            xfrTask.Properties("CopyAllObjects").SetValue(xfrTask, False)
            xfrTask.Properties("CopyAllTables").SetValue(xfrTask, False)

            xfrTask.Properties("TablesList").SetValue(xfrTask, Tables)
            xfrTask.Properties("DropObjectsFirst").SetValue(xfrTask, True)
            xfrTask.Properties("CopyData").SetValue(xfrTask, True)
            xfrTask.Properties("CopySchema").SetValue(xfrTask, True)
            xfrTask.Properties("ExistingData").SetValue(xfrTask, 0)
            xfrTask.Properties("CopyPrimaryKeys").SetValue(xfrTask, False)
            xfrTask.Properties("CopyIndexes").SetValue(xfrTask, False)
            xfrTask.Properties("CopyTriggers").SetValue(xfrTask, False)

            'Below code is used to save the package in 
            'physical file ".dtsx" for verification purposes.
            'Dim app As Application = New Application()
            'Dim strFileName As String = Hour(DateTime.Now()) & Minute(DateTime.Now())
            'Below code to create DTSX package physical file for testing
            'app.SaveToXml("D:\DynamicPackage_" & strFileName & ".dtsx", pkg, Nothing)

            pkg.Execute()
            pkg.Dispose()

            Dts.TaskResult = ScriptResults.Success
 
    End Sub 

Points of Interest

I tried this code with OLEDB and ADO connection, but it did not work, “Transfer SQL Server Objects Task” works with Server Management Objects (SMOServer) connections.

History

• 16^th February, 2019: Initial version