January 12, 2016 at 11:51 am
Exception thrown by target of 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()
My VB.NET ssis script task transfers data from some tables in one database to another. Table schemas is same. Error occurs for a table that is created on the fly. This code works fine in SQL 2005 environment. SSIS package is executed through a SQL agent job but fails when executed in visual studio as well. Script code as follows. Error thrown for first table in the transfer list
xfr.ObjectList.Add(dbSource.Tables(Dts.Variables("TableName").Value.ToString, "dbo"))
the table name is tbl_FTP_201512. Every month a new table is created on the fly by the ETL process with subsequent month (e.g. 201511, 201512). I dont see any code that creates this table in the destination database but the same code works fine in SQL 2005.
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
Public Sub Main()
'Define source and dest databases/servers
Dim dbSourceName As String = "Treasury_Stage"
Dim dbDestName As String = "Treasury"
Dim SrcSrv As Server
SrcSrv = New Server(Dts.Variables("StagingServerName").Value.ToString)
Dim DestSrv As Server
DestSrv = New Server(Dts.Variables("ProductionServerName").Value.ToString)
Dim dbSource As Database
dbSource = SrcSrv.Databases(dbSourceName)
Dim dbDest As Database
dbDest = DestSrv.Databases(dbDestName)
'Initiate transfer
Dim xfr As Transfer
xfr = New Transfer(dbSource)
'Transfer options (copy schema, implement identity as-is, dtop destination if exists)
xfr.CopyAllObjects = False
xfr.DestinationLoginSecure = True
xfr.DropDestinationObjectsFirst = False
xfr.CopySchema = False
xfr.CopyData = True
xfr.Options.NoIdentities = True
xfr.Options.WithDependencies = False
xfr.DestinationDatabase = dbDest.Name
xfr.DestinationServer = DestSrv.Name
'List of objects to transfer
xfr.ObjectList.Add(dbSource.Tables(Dts.Variables("TableName").Value.ToString, "dbo"))
xfr.ObjectList.Add(dbSource.Tables("tbl_QC_Results", "dbo"))
xfr.ObjectList.Add(dbSource.Tables("tbl_FTP_Errors", "dbo"))
'Actually perform the transfer
xfr.TransferData()
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
January 12, 2016 at 1:36 pm
Realized that I had to set xfr.DropDestinationFirst and xfr.CopySchema to True.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply