Reputation: 193
I'm using a script to move multiple tables/ indexes that I got from http://www.sqltechnet.com/2012/08/ssis-transfer-sql-server-object-using.html When I input the references and the script into a VB script task. I'm getting two errors in the building "transfer is not defined"
From Lines: Dim xfr As Transfer xfr = New Transfer(db)
How do I fix these two errors? I'm using "SQL Server Data Tools for Visual Studio 2013"
#
Region "Imports"
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime# End Region 'ScriptMain is the entry point class of the script. Do not change the name, attributes,
'
or parent of this class. < Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute() > _ < System.CLSCompliantAttribute(False) > _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase 'This method is called when this script task executes in the control flow.
'
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'To open Help, press F1.
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 = ScriptResults.Success
End Sub# Region "ScriptResults declaration"
'This enum provides a convenient shorthand within the scope of this class for setting the
'
result of the script.
'This code was generated automatically.
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
#End Region
End Class
Upvotes: 0
Views: 901
Reputation: 193
Looks like you have to have the Assembly referenced : Microsoft.SqlServer.SmoExtended (in Microsoft.SqlServer.SmoExtended.dll) Once I added the Microsoft.SqlServer.SmoExtended.dll it worked fine.
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx
Upvotes: 1