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:
- Create SSIS package for each table using
DataFlow
task and it will take care of creating table and moving data. - 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
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;"
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
Dim xfr As Executable = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask")
Dim xfrTask As TaskHost = CType(xfr, TaskHost)
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)
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
- 16th February, 2019: Initial version