dbahiker
dbahiker

Reputation: 193

using smo in SSIS 2014 script task vb "transfer is not defined"

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

Answers (1)

dbahiker
dbahiker

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

Related Questions