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.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.
Thanks a lot! Your article is pretty good. I like it
ReplyDeleteSQL server will definitely be very helpful to face these type of situations very easily. coursework point | buy coursework online | do my coursework
ReplyDeleteExcellent and Awonderful read! Your article is the best one I haave learnt.
ReplyDeleteFantastic Post.thanks for share..a lot more wait ..
ReplyDeleteThanks for useful information.
ReplyDeleteHi, after using this method I realised the table schema is not specified. Do you know how to specify the schema?
ReplyDeleteThanks!
Get the best essay help right now online!
ReplyDeleteWhat if I want to copy Different tables to different target MDF files..
ReplyDeleteThanks,
Saurabh
I am really glad to see this post. thanks. customessayonline.com/buy-essay
ReplyDeleteMM, 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
ReplyDeletewonderful blog but i 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.
ReplyDeletecoursework Writing Service
ecommerce portal development services in delhi
ReplyDeleteecommerce designing in janakpuri
ecommerce designing in uttamnagar
ReplyDeleteThanks For Sharing such a good Information
Seo services in Delhi
Seo in Delhi
Seo company in Delhi
SEO expert Delhi
Seo agency in Delhi
best seo services in delhi ncr
seo consultant delhi
nice post.
ReplyDeletewebsite designing in uttamnagar
This comment has been removed by the author.
ReplyDeletejust-vitamins Vouchers Codes
ReplyDeletewaterstonesVouchers Codes
lands-endVouchers Codes
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system. It is very important for online business owners. Masters dissertation writing service.
ReplyDeleteHere I am sharing a very useful resource for students looking for assignment help. ProAssignmentHelp providing all sort of assignment writing in various subjects like as : Assignment Help Australia , Programming Assignment Help , Machine learning Assignment Help, MATLAB Assignment Help , Python Assignment Help, etc.
ReplyDelete………………………………………………………………….
Here I am sharing a very useful resource for students looking for assignment help. ProAssignmentHelp providing all sort of assignment writing in various subjects like as : Assignment Help Australia , Programming Assignment Help Machine learning Assignment Help MATLAB Assignment Help Python Assignment Help, etc.
Here I am sharing a very useful resource for students looking for assignment help. ProAssignmentHelp providing all sort of assignment writing in various subjects like as : Assignment Help Australia , Programming Assignment Help , Machine learning Assignment Help, MATLAB Assignment Help , Python Assignment Help, etc.
ReplyDelete………………………………………………………………….
Here I am sharing a very useful resource for students looking for assignment help. ProAssignmentHelp providing all sort of assignment writing in various subjects like as : Assignment Help Australia , Programming Assignment Help Machine learning Assignment Help MATLAB Assignment Help Python Assignment Help, etc.
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. Safe Way of Using an Essay Writing Service
ReplyDeleteThank you so much for exploring the best and right information.
ReplyDeleteSSIS Upsert
It's such a great resource that you are sharing and it's for free. I love seeing blog that understand the value of providing a quality resource for free like GBWhatsApp. It’s hard to find nowadays to know about the basics but you did it so much well. Keep up the good work
ReplyDeleteThanks for the detailed article on this topic. I would like to see more such awesome articles from you.
ReplyDeleteEven though I am here for the first time, I am very impressed with your post. Thanks for sharing.
ReplyDeleteThanks for sharing this valuable piece of information. Keep sharing more such awesome articles in the future. Goodbye!
ReplyDeleteThank you for the post sir. Now I can Transfer SQL Server Object using SMO.
ReplyDeleteTODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com
ReplyDeleteHello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com
Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.
Thanks for the detailed article on this topic. I would like to see more such awesome articles from you.
ReplyDeleteAlso you can get the new and best features of FMWhatsapp which are coming in 2022-
FMWhatsapp APK
Very good explanation. Impressive post, I like your page, it was very useful for me.
ReplyDelete