December 1, 2015 at 4:20 pm
Hello all,
Below is my VB.NET script code in a ssis script task. It fails at xfr.TransferData() with error:
Exception has been thrown by the target of an invocation.
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
I am using exact same code in another script task that does the same thing in another package. Only difference is that source and destination server names are reversed for the transfer the other way. When I remove all tables from the list but the first one then it works fine. But I need all tables. I know it fails at xfr.TransferData() call because I put a msg box alert right before and after that call. The one before prints out. The one after does not and error window pops up.
MY CODE:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
'this is basically a 'roll your own' Transfer Object Task that can handle dynamic table names, constraints, etc.
'using script method because schemas between stage and prod are different
'
'Declaration
Public Sub Main()
'
Dim dbSourceName As String = "NetOx_Stage"
Dim dbDestName As String = "NetOx"
Dim strSrcSvr As String, strDestSvr As String
'Connect to the servers - Src is default
strDestSvr = GetServerFromConManager("NetOx")
strSrcSvr = GetServerFromConManager("NetOx_Stage")
Dim SrcSrv As Server
SrcSrv = New Server(strSrcSvr)
Dim DestSrv As Server
DestSrv = New Server(strDestSvr)
'Reference the source database
Dim db As Database
db = SrcSrv.Databases(dbSourceName)
'Create a new database that is to be destination database.
Dim dbCopy As Database
dbCopy = DestSrv.Databases(dbDestName)
'Define a Transfer object and set the required options.
Dim xfr As Transfer
xfr = New Transfer(db)
'set all options
xfr.CopyAllObjects = False
xfr.DestinationLoginSecure = True
'These next two options should only be set if the table schemas in Reporting are
'the same as in Staging. Otherwise, comment them out.
xfr.DropDestinationObjectsFirst = False
xfr.CopySchema = False
xfr.CopyData = True
'this option allows for identity cols to be pushed as is
xfr.Options.NoIdentities = False
xfr.Options.WithDependencies = False
xfr.Options.Indexes = True
xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = DestSrv.Name
''transfer the tables listed here
xfr.ObjectList.Add(db.Tables("tbl_Dim_Actn_Taken", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_App_Stat", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Apprvl_Stat", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Batch", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Deny_Rsn", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Loan_Purp", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Loan_Type", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Nbr_Units", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_Prop_Type", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_USB_Footprint", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Dim_UW_Cmpnst_Fctr", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Err_Loan_Nbr", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_QC_Master", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Xref_Loan_Nbr", "dbo"))
xfr.ObjectList.Add(db.Tables("tbl_Xref_USBHM_HELOC_Nbr", "dbo"))
'Execute the transfer
xfr.TransferData()
Dts.TaskResult = ScriptResults.Success
End Sub
Public Function GetServerFromConManager(ByVal ConManagerName As String) As String
'get connection string from connection manager
Dim ConString As String = Dts.Connections(ConManagerName).ConnectionString.ToString
'parse the string on ; and = , get 2nd value (zero based array so get (1))
Dim ServerName As String = ConString.Split(New Char() {"="c, ";"c})(1)
'MsgBox(ServerName)
Return ServerName
End Function
End Class
December 2, 2015 at 2:02 pm
Never mind. I found out that one of the tables being transferred had slightly different data type for one of the columns. CHAR(1) vs CHAR(4). It never failed when using the SQL Transfer Object Task in SQL 2005. We migrated to SQL 2014 and replaced the SMO Transfer Object Task with a Script task utilizing the SMO object extension.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply