Cheers.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<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
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Dim var As Variables
Public Sub Main()
'Package Variable Declaration
'Read
Dts.VariableDispenser.LockForRead("User::str_PkColumn")
Dts.VariableDispenser.LockForRead("User::str_BatchId")
Dts.VariableDispenser.LockForRead("User::str_SourceArchiveTable")
Dts.VariableDispenser.LockForRead("User::bit_IsRetention")
Dts.VariableDispenser.LockForRead("User::int_ArchiveExportId")
Dts.VariableDispenser.LockForRead("User::int_FastLoadBatchSize")
Dts.VariableDispenser.LockForRead("User::int_FastLoadCommit")
Dts.VariableDispenser.LockForRead("User::bit_IsFastLoad")
Dts.VariableDispenser.LockForRead("User::bit_IsSqlCmd")
Dts.VariableDispenser.LockForRead("User::bit_IsSqlOverride")
Dts.VariableDispenser.LockForRead("User::bit_IsTransferData")
Dts.VariableDispenser.LockForRead("User::str_AutoSqlCmd")
Dts.VariableDispenser.LockForRead("User::str_DestinationDatabase")
Dts.VariableDispenser.LockForRead("User::str_DestinationSchema")
Dts.VariableDispenser.LockForRead("User::str_DestinationServer")
Dts.VariableDispenser.LockForRead("User::str_DestinationTable")
Dts.VariableDispenser.LockForRead("User::str_SourceDatabase")
Dts.VariableDispenser.LockForRead("User::str_SourceSchema")
Dts.VariableDispenser.LockForRead("User::str_SourceServer")
Dts.VariableDispenser.LockForRead("User::str_SourceTable")
Dts.VariableDispenser.LockForRead("User::str_SqlCmd")
Dts.VariableDispenser.LockForRead("User::str_SqlFilter")
'Write
Dts.VariableDispenser.LockForWrite("User::int_IsPkgFail")
Dts.VariableDispenser.LockForWrite("User::str_XmlDoc")
Dts.VariableDispenser.LockForWrite("User::int_DestCurrCnt")
Dts.VariableDispenser.LockForWrite("User::int_DestPrevCnt")
Dts.VariableDispenser.LockForWrite("User::int_SourceCnt")
Dts.VariableDispenser.GetVariables(var)
'local variables
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package
Dim DestPrevCnt As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("int_DestPrevCnt", False, "User", 0)
Dim DestCurrCnt As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("int_DestCurrCnt", False, "User", 0)
Dim SourceCnt As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("int_SourceCnt", False, "User", 0)
Dim BatchId As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("str_BatchId", False, "User", var("User::str_BatchId").Value.ToString())
Dim PkColumn As String = var("User::str_PkColumn").Value.ToString()
pkg.Variables.Add("int_ArchiveExportId", False, "User", CType(var("User::int_ArchiveExportId").Value, Int32))
Dim ArchiveExportId As Int32
Dim FastLoadMaxCommitSize As Int32
Dim RowsPerBatch As String
Dim DestCnt As Microsoft.SqlServer.Dts.Runtime.TaskHost
Dim DestCurr As Microsoft.SqlServer.Dts.Runtime.TaskHost
Dim DataFlowTaskHost As Microsoft.SqlServer.Dts.Runtime.TaskHost
Dim SourceComponent As IDTSComponentMetaData100
Dim SourceRowCount As IDTSComponentMetaData100
Dim DestinationComponent As IDTSComponentMetaData100
Dim MultiCastComponent As IDTSComponentMetaData100
Dim SourceInstance As CManagedComponentWrapper
Dim rowInstance As CManagedComponentWrapper
Dim DestinationInstance As CManagedComponentWrapper
Dim MultiCastInstace As CManagedComponentWrapper
Dim BathIdColumn As IDTSOutputColumn100 ' Bathid column'
Dim TableNameColumn As IDTSOutputColumn100 'Table Name'
Dim PkIdColumn As IDTSOutputColumn100 'Primary Key Column '
Dim ArchiveExportIdColumn As IDTSOutputColumn100
Dim ArchiveExportIdProperty As IDTSCustomProperty100
Dim BathIdProperty As IDTSCustomProperty100
Dim TableNameColumnProperty As IDTSCustomProperty100
Dim PkIdColumnProperty As IDTSCustomProperty100
Dim PkIdInputColumn As IDTSInputColumn100
Dim DestinationVirtualInput As IDTSVirtualInput100
Dim DestinationVirtualInputColumns As IDTSVirtualInputColumnCollection100
Dim DestinationInput As IDTSInput100
Dim SourceCntPath As IDTSPath100
Dim SourceMultiCastPath As IDTSPath100
Dim DeriveColumnMulticastPath As IDTSPath100
Dim DeriveColumnArchiveIDPath As IDTSPath100
Dim DestinationPath As IDTSPath100
Dim DataFlowTask As MainPipe
Dim DestcurrRB As IDTSResultBinding
Dim DestCurr1 As ExecuteSQLTask
Dim DestCnt1 As ExecuteSQLTask
Dim DataFlow As Executable
Dim DestRB As IDTSResultBinding
Dim SqlCounts As String = "Select count(1) from "
Dim SqlCmdForId As String = "Select " + PkColumn + " From "
Dim Failure As Int32 = -999
Dim PkgLogging As LogProvider
Dim seg As Executable
Dim eDestCnt As Executable
Dim eDestCurr As Executable
Dim intDestPrev As Int32
Dim intDestCurrCnt As Int32
Dim intSourceCnt As Int32
Dim seg1 As Microsoft.SqlServer.Dts.Runtime.Sequence
Dim seg2 As Microsoft.SqlServer.Dts.Runtime.Sequence
Dim seg3 As Microsoft.SqlServer.Dts.Runtime.Sequence
Dim seg4 As Microsoft.SqlServer.Dts.Runtime.Sequence
'Connection Information
Dim SourceConStr As String = Dts.Connections("Source").ConnectionString
Dim DestinationConStr As String = Dts.Connections("Destination").ConnectionString
Dim HostConStr As String = Dts.Connections("Host").ConnectionString
Dim MsdbConStr As String = Dts.Connections("msdb").ConnectionString
Dim SourceSqlCmd As String = var("User::str_SqlCmd").Value.ToString
Dim AutoSqlCmd As String = var("User::str_AutoSqlCmd").Value.ToString
Dim ExecuteSqlCmd As String
Dim SourceSqlFilter As String = var("User::str_SqlFilter").Value.ToString
Dim PackageName As String = "ArchiveExportv2005000_"
Dim IsFastLoad As New Boolean
Dim IsOverride As New Boolean
Dim IsQuery As New Boolean
Dim IsTransferData As New Boolean
Dim IsRetention As New Boolean
'Source Variables
Dim SourceSchema As String = var("User::str_SourceSchema").Value.ToString()
Dim SourceTable As String = var("User::str_SourceTable").Value.ToString()
Dim SourceSchTable As String = "[" + var("User::str_SourceDatabase").Value.ToString() + "]." + "[" + SourceSchema + "].[" + SourceTable + "]"
Dim SourceServer As String = "[" + var("User::str_SourceServer").Value.ToString() + "]"
Dim TableName As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("str_TableName", False, "User", SourceServer.ToString() + "." + SourceSchTable.ToString())
Dim pkgServerName As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("str_SourceServerName", False, "User", SourceServer.ToString())
'Destination Variables
Dim DestinationSchema As String = var("User::str_DestinationSchema").Value.ToString()
Dim DestinationTable As String = var("User::str_DestinationTable").Value.ToString()
Dim SourceArchive As String = var("User::str_SourceArchiveTable").Value.ToString()
Dim DestinationSchTable As String = "[" + DestinationSchema + "].[" + DestinationTable + "]"
SqlCmdForId = SqlCmdForId + DestinationSchTable + " With (nolock)"
'Variable Initialization
FastLoadMaxCommitSize = CType(var("User::int_FastLoadCommit").Value, Int32)
ArchiveExportId = CType(var("User::int_ArchiveExportId").Value, Int32)
RowsPerBatch = var("User::int_FastLoadBatchSize").Value.ToString()
pkg.Name = "Ssis_v2005_ArchiveExportId_" + CType(ArchiveExportId, String)
'pkg.Variables.Add("int_PkId", False, "User", -1)
'pkg.Variables.Add("int_GroupId", False, "User", -1)
pkg.Variables.Add("extract_date", False, "User", DateTime.Now)
IsFastLoad = CType(var("User::bit_IsFastLoad").Value, Boolean)
IsOverride = CType(var("User::bit_IsSqlOverride").Value, Boolean)
IsQuery = CType(var("User::bit_IsSqlCmd").Value, Boolean)
IsTransferData = CType(var("User::bit_IsTransferData").Value, Boolean)
IsRetention = CType(var("User::bit_IsRetention").Value, Boolean)
' Add Connections
Dim SourceConMgr As ConnectionManager = CreateOLEDBConnection(pkg, "Source", SourceConStr)
Dim DestinationConMgr As ConnectionManager = CreateOLEDBConnection(pkg, "Destination", DestinationConStr)
Dim msdb As ConnectionManager = CreateOLEDBConnection(pkg, "msdb", MsdbConStr)
Dim host As ConnectionManager = CreateOLEDBConnection(pkg, "host", HostConStr)
' Add Logging
PkgLogging = pkg.LogProviders.Add("DTS.LogProviderSQLServer.1")
PkgLogging.ConfigString = msdb.Name
PkgLogging.Description = "Packing logging Using Host msdb"
pkg.LoggingOptions.SelectedLogProviders.Add(PkgLogging)
pkg.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion
pkg.LoggingOptions.EventFilter = New String() {"OnPreExecute", "OnPostExecute", "OnError"}
pkg.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled
'Set Isolation Level
pkg.IsolationLevel = IsolationLevel.ReadUncommitted
'Set Sequences
seg = pkg.Executables.Add("STOCK:Sequence")
seg1 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
seg2 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
seg3 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
seg4 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
seg1.Name = "ArchiveTable_Sequence1"
seg2.Name = "ArchiveTable_Sequence2"
seg3.Name = "ArchiveId_Sequence"
Try
'Set Sql Task
'Destination Previoscount
eDestCnt = seg1.Executables.Add("STOCK:SQLTASK")
DestCnt = CType(eDestCnt, Microsoft.SqlServer.Dts.Runtime.TaskHost)
DestCnt.FailPackageOnFailure = True
DestCnt.IsolationLevel = IsolationLevel.ReadCommitted
DestCnt.Name = "DestinationPrevious"
DestCnt.Properties("Connection").SetValue(DestCnt, DestinationConMgr.ID)
DestCnt.Properties("SqlStatementSource").SetValue(DestCnt, SqlCounts + " " + DestinationSchTable)
DestCnt.Properties("BypassPrepare").SetValue(DestCnt, False)
DestCnt.SetExpression("ResultSetType", "2") '2 = single row
DestCnt.ExecValueVariable = DestPrevCnt
DestCnt1 = CType(DestCnt.InnerObject, ExecuteSQLTask)
DestRB = DestCnt1.ResultSetBindings.Add()
DestRB.DtsVariableName = "User::int_DestPrevCnt"
'Destination CurrentCount
eDestCurr = seg2.Executables.Add("STOCK:SQLTASK")
DestCurr = CType(eDestCurr, Microsoft.SqlServer.Dts.Runtime.TaskHost)
DestCurr.FailPackageOnFailure = True
DestCurr.IsolationLevel = IsolationLevel.ReadUncommitted
DestCurr.Name = "DestinationCurret"
DestCurr.Properties("Connection").SetValue(DestCurr, DestinationConMgr.ID)
DestCurr.Properties("SqlStatementSource").SetValue(DestCurr, SqlCounts + " " + DestinationSchTable)
DestCurr.Properties("BypassPrepare").SetValue(DestCurr, False)
DestCurr.SetExpression("ResultSetType", "2") '2 = single row
DestCnt.ExecValueVariable = DestCurrCnt
DestCurr1 = CType(DestCurr.InnerObject, ExecuteSQLTask)
DestcurrRB = DestCurr1.ResultSetBindings.Add()
DestcurrRB.DtsVariableName = "User::int_DestCurrCnt"
'Set Execution Query
If IsQuery = True And IsRetention = True Then
ExecuteSqlCmd = SourceSqlCmd
Else
ExecuteSqlCmd = AutoSqlCmd
End If
'DataFlow task
DataFlow = seg3.Executables.Add("STOCK:PipelineTask")
DataFlowTaskHost = CType(DataFlow, Microsoft.SqlServer.Dts.Runtime.TaskHost)
DataFlowTaskHost.Name = "ArchiveExportDataFlow"
DataFlowTask = CType(DataFlowTaskHost.InnerObject, MainPipe)
'Join containers
Dim DestToWf As Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint = seg1.PrecedenceConstraints.Add(eDestCnt, DataFlow)
DestToWf.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Dim WfToDest As Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint = seg2.PrecedenceConstraints.Add(DataFlow, eDestCurr)
WfToDest.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
'Add Source Component
SourceComponent = DataFlowTask.ComponentMetaDataCollection.[New]
SourceComponent.Name = "SourceTable"
SourceComponent.ComponentClassID = "DTSAdapter.OleDbSource.1"
'Get Source instance
SourceInstance = SourceComponent.Instantiate
SourceInstance.ProvideComponentProperties()
'Set Source Connection
SourceComponent.RuntimeConnectionCollection(0).ConnectionManagerID = SourceConMgr.ID
SourceComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(SourceConMgr)
If IsQuery = True Then
'Use Query for source transfer
SourceInstance.SetComponentProperty("AccessMode", 2) '2 - SQL Command
SourceInstance.SetComponentProperty("SqlCommand", ExecuteSqlCmd)
Else
'Use straight table transfer
SourceInstance.SetComponentProperty("AccessMode", 0) '0 TableOrView
End If
SourceInstance.SetComponentProperty("OpenRowset", SourceSchTable)
'Reinitialize the metadata, Refresh Columns
SourceInstance.AcquireConnections(Nothing)
SourceInstance.ReinitializeMetaData()
SourceInstance.ReleaseConnections()
'Add Multicast
MultiCastComponent = DataFlowTask.ComponentMetaDataCollection.[New]
MultiCastComponent.ComponentClassID = "DTSTransform.Multicast.1"
'Get Mulicast Instance
MultiCastInstace = MultiCastComponent.Instantiate
MultiCastInstace.ProvideComponentProperties()
MultiCastComponent.Name = "Mulitcast Source Data"
'Add Row Count component
SourceRowCount = DataFlowTask.ComponentMetaDataCollection.[New]
SourceRowCount.ComponentClassID = "DTSTransform.RowCount.1"
rowInstance = SourceRowCount.Instantiate
rowInstance.ProvideComponentProperties()
rowInstance.SetComponentProperty("VariableName", "User::int_SourceCnt")
'Connect multicast and Source
SourceMultiCastPath = DataFlowTask.PathCollection.[New]
SourceMultiCastPath.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), MultiCastComponent.InputCollection(0))
'Connect the Multicast And Row count
SourceCntPath = DataFlowTask.PathCollection.[New]
SourceCntPath.AttachPathAndPropagateNotifications(MultiCastComponent.OutputCollection(0), SourceRowCount.InputCollection(0))
'Add Table Destination
' Add OLE-DB destination
DestinationComponent = DataFlowTask.ComponentMetaDataCollection.New()
DestinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
DestinationComponent.ValidateExternalMetadata = True
'Get Destination Instance and initialize component
DestinationInstance = DestinationComponent.Instantiate
DestinationInstance.ProvideComponentProperties()
DestinationComponent.Name = "Archive"
DestinationComponent.Description = "Archive table"
'Set Destination Connections
DestinationComponent.RuntimeConnectionCollection(0).ConnectionManagerID = DestinationConMgr.ID
DestinationComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DestinationConMgr)
'Use straight table transfer
If IsFastLoad = False Then
DestinationInstance.SetComponentProperty("AccessMode", 0)
Else
DestinationInstance.SetComponentProperty("AccessMode", 3)
' DestinationArchiveInstance.SetComponentProperty("AccessMode", 3)
End If
DestinationInstance.SetComponentProperty("OpenRowset", DestinationSchTable)
DestinationInstance.SetComponentProperty("FastLoadKeepIdentity", True)
DestinationInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", FastLoadMaxCommitSize)
DestinationInstance.SetComponentProperty("FastLoadKeepNulls", False)
DestinationInstance.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = " + RowsPerBatch)
DestinationInstance.Validate()
DestinationInstance.AcquireConnections(Nothing)
DestinationInstance.ReinitializeMetaData()
DestinationInstance.ReleaseConnections()
'Add Rowcount and Archive Destination
DestinationPath = DataFlowTask.PathCollection.New()
DestinationPath.AttachPathAndPropagateNotifications(SourceRowCount.OutputCollection(0), DestinationComponent.InputCollection(0))
DestinationInput = DestinationComponent.InputCollection(0)
DestinationVirtualInput = DestinationInput.GetVirtualInput
DestinationVirtualInputColumns = CType(DestinationVirtualInput.VirtualInputColumnCollection, IDTSVirtualInputColumnCollection100)
'Mapping
For Each vcolumn As IDTSVirtualInputColumn100 In DestinationVirtualInput.VirtualInputColumnCollection
Dim inputColumn As IDTSInputColumn100 = DestinationInstance.SetUsageType(DestinationInput.ID, DestinationVirtualInput, vcolumn.LineageID, DTSUsageType.UT_READONLY)
Dim externalColumn As IDTSExternalMetadataColumn100 = DestinationInput.ExternalMetadataColumnCollection(inputColumn.Name)
DestinationInstance.MapInputColumn(DestinationInput.ID, inputColumn.ID, externalColumn.ID)
Next
If IsTransferData = True Then
pkg.Execute()
If (pkg.ExecutionResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure) Or (pkg.ExecutionStatus = Microsoft.SqlServer.Dts.Runtime.DTSExecStatus.Abend) Then
SaveFailPackage(pkg)
' Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
' App.SaveToXml(String.Format("C:\Temp\ssis_ERROR1_" + SourceTable + "-" + DestinationTable + "_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)
var("User::int_IsPkgFail").Value = CType(1, Int32)
var("User::int_DestCurrCnt").Value = Failure
var("User::int_DestPrevCnt").Value = Failure
var("User::int_SourceCnt").Value = Failure
Dts.Events.FireError(-1, "Internal Package Error", "Internal Dynamic package Error For ArchiveExportId: " + CType(ArchiveExportId, String) + " ErrorMessage:" + GetErrorMessage(pkg), "", 0)
Else
' Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
' App.SaveToXml(String.Format("C:\Temp\ssis_" + SourceTable + "-" + DestinationTable + "_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)
var("User::int_IsPkgFail").Value = CType(0, Int32)
intDestPrev = CType(DestPrevCnt.Value, Int32)
intDestCurrCnt = CType(DestCurrCnt.Value, Int32)
intSourceCnt = CType(SourceCnt.Value, Int32)
var("User::int_DestCurrCnt").Value = intDestCurrCnt
var("User::int_DestPrevCnt").Value = intDestPrev
var("User::int_SourceCnt").Value = intSourceCnt
End If
End If
var.Unlock()
pkg = Nothing
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
' App.SaveToXml(String.Format("C:\Temp\ssis_ERROR_" + SourceTable + "-" + DestinationTable + "_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)
SaveFailPackage(pkg)
'App = Nothing
pkg = Nothing
var.Unlock()
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
Private Function CreateOLEDBConnection(ByVal p As Microsoft.SqlServer.Dts.Runtime.Package, _
ByVal ConName As String, ByVal ConStr As String) As ConnectionManager
Dim ConMgr As ConnectionManager = p.Connections.Add("OLEDB")
ConMgr.ConnectionString = ConStr
ConMgr.Name = ConName
ConMgr.Description = "SQL OLE DB Using " + ConName + " Connection"
Return ConMgr
End Function
Private Sub SaveFailPackage(ByVal pkg As Microsoft.SqlServer.Dts.Runtime.Package)
Dim SQLCon As New SqlClient.SqlConnection
Dim pkid As Int32 = CType(var("User::int_ArchiveExportId").Value, Int32)
Dim SQL As String = "[dbo].[usp_SsisStorage_InsUpd]"
Dim Host As ConnectionManager = Dts.Connections("Host")
Dim ServerName As String = CStr(Host.Properties("ServerName").GetValue(Host))
Dim DatabaseName As String = CStr(Host.Properties("InitialCatalog").GetValue(Host))
Dim Connstr As String = "Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True"
Dim myPkgDoc As XmlDocument = New XmlDocument()
pkg.SaveToXML(myPkgDoc, Nothing, Nothing)
SQLCon.ConnectionString = Connstr
SQLCon.Open()
Dim sqlCmd As New SqlClient.SqlCommand(SQL, SQLCon)
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.AddWithValue("ArchiveExportId", pkid)
sqlCmd.Parameters.AddWithValue("pkg", myPkgDoc.OuterXml)
sqlCmd.ExecuteNonQuery()
SQLCon.Close()
SQLCon = Nothing
sqlCmd = Nothing
End Sub
Function GetErrorMessage(ByVal p As Microsoft.SqlServer.Dts.Runtime.Package) As String
Dim ErrorMessage As StringBuilder = New StringBuilder
For Each Message As Microsoft.SqlServer.Dts.Runtime.DtsError In p.Errors
ErrorMessage.Append(Message.Description.ToString())
Next
Return ErrorMessage.ToString()
End Function
End Class
No comments:
Post a Comment