Using SSIS VB.NET Script Task to append SQL 2014 table data not replace it

• farazkhan1981

  Old Hand

  Points: 398

  More actions

  January 14, 2016 at 7:49 am

  #303761

  Hello all. We are migrating to SQL 2014 from SQL 2005. We have to update our ETL packages to SQL 2014. Part of that is to replace all SQL Server Transfer Object tasks to VB.NET script tasks in our packages as SQL 2014 SSDT no longer have SMO transfer object tasks. I have the following VB.NET script task code which does work but I want to know if it replaces all data in the destination table or appends new data to it. We want to append data and not replace it entirely. We are copying data from a table in a staging database to the same table in a production database.

  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()

  '

  Dim dbSourceName As String = "SourceDB"

  Dim dbDestName As String = "DestDB"

  Dim strSrcSvr As String, strDestSvr As String

  'Connect to the servers - Src is default

  strDestSvr = GetServerFromConManager("DestDB")

  strSrcSvr = GetServerFromConManager("SourceDB")

  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_Corp_Mid_Errors", "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