Wednesday, 8 August 2012

SSIS: Transfer SQL Server Object using SMO


we often face a situation to move SQL server object beween servers or databases, when you have constant table list we can always use "Transfer SQL Server Objects Task" with the SSIS, One of my customer had a requirement of scheduling migration process frequently and would be supplying the table list dynamic every time using a meta data table. When i started to develop the SSIS to accomplish this task i realised the "Transfer SQL Server Objects Task" does not accept variable of Object data type with the table list as expression. So i decided to use SMO to copy SQL server objects using xfr.TransferData() function which has more flexible option.
You can change various options to the below script, I have created the script to create a table list and migrate to destination with data.
 
First you'll need to add references to the three SMO assemblies (found under Microsoft SQL Server\90\SDK\Assemblies):
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
 
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
 
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim dbSourceName As String
Dim dbDestName As String
Dim tbl_name1 As String
Dim SQLServername As String
'Set Source and destination database
dbSourceName = Dts.Variables("sourcedb_name").Value.ToString()
dbDestName = Dts.Variables("destdb_name").Value.ToString()
'Connect to the local, default instance of SQL Server.
SQLServername = Dts.Variables("SQL_Name").Value.ToString()
Dim srv As Server
srv = New Server(SQLServername)
'Reference the source database
Dim db As Database
db = srv.Databases(dbSourceName)
'Reference the destination database.
Dim dbCopy As Database
dbCopy = New Database(srv, dbDestName)
'Table name
tbl_name1 = Dts.Variables("Tbls_name").Value.ToString()
'Define a Transfer object and set the required options.
'MsgBox(tbl_name1)
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllDatabaseTriggers = False
xfr.CopyAllDefaults = False
xfr.CopyAllLogins = False
xfr.CopyAllObjects = False
xfr.CopyAllPartitionFunctions = False
xfr.CopyAllPartitionSchemes = False
xfr.CopyAllRoles = False
xfr.CopyAllRules = False
xfr.CopyAllSchemas = False
xfr.CopyAllSqlAssemblies = False
xfr.CopyAllStoredProcedures = False
xfr.CopyAllSynonyms = False
xfr.CopyAllTables = False
xfr.CopyAllUserDefinedAggregates = False
xfr.CopyAllUserDefinedDataTypes = False
xfr.CopyAllUserDefinedFunctions = False
xfr.CopyAllUserDefinedTypes = False
xfr.CopyAllUsers = False
xfr.CopyAllViews = False
xfr.CopyAllXmlSchemaCollections = False
xfr.CreateTargetDatabase = False
xfr.DropDestinationObjectsFirst = False
xfr.PrefetchObjects = False
xfr.SourceTranslateChar = False
'Add Table to the List
xfr.ObjectList.Add(db.Tables(tbl_name1))
xfr.Options.WithDependencies = True
xfr.Options.Indexes = True
xfr.Options.DriAll = True
xfr.CopySchema = True
xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = srv.Name
'Include data
xfr.CopyData = True
 
'Execute the transfer
xfr.TransferData()
 
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

16 comments:

  1. Thanks a lot! Your article is pretty good. I like it

    ReplyDelete
  2. SQL server will definitely be very helpful to face these type of situations very easily. coursework point | buy coursework online | do my coursework


    ReplyDelete
  3. Excellent and Awonderful read! Your article is the best one I haave learnt.

    ReplyDelete
  4. Fantastic Post.thanks for share..a lot more wait ..

    ReplyDelete
  5. Hi, after using this method I realised the table schema is not specified. Do you know how to specify the schema?

    Thanks!

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thanks for the post :) Though I am not an expert in this field.

    ReplyDelete
  8. This blog is fantastic. That's not really a really huge statement, but its all I could come up with after reading this. You know so much about this subject. So much so that you made me want to learn more about it. Your blog is my stepping stone, my friend. Thanks for the heads up on this post. air conditioning installation denver

    ReplyDelete
  9. What if I want to copy Different tables to different target MDF files..

    Thanks,
    Saurabh

    ReplyDelete
  10. MM, interesting idea to use xfr. for servers. Thank for detailed explaining on how to do that right! Keep on writing! If some day you need help in writing then go to http://www.papers-writings.com

    ReplyDelete
  11. Highly experienced custom writers available online 24/7!

    ReplyDelete
  12. Custom academic writers available online 24/7!

    ReplyDelete
  13. Earlier people used to transfer only textual data by net that was very easily forwarded by email connection but now everyone loves to share pictures, audios as well as movies with their loved ones.
    https://downloadshareitapp.com

    ReplyDelete