-
dts package in visual basic
Hi All,
I created a package that will connect to a table in sql server. The table contains table_name which has to be exported from the source database to the destination database. My package seems to work okay. Except that some of the table in the source database has dependant procedures and views which I want to export to my destination table. Write now only the tables and its dependant tables are being exported.
My package looks something like this!
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "pkg_name"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Copy SQL Server Objects"
oStep.Description = "Copy SQL Server Objects"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy SQL Server Objects"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
Call Task_Sub1(goPackage)
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.UnInitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = Nothing
Set goPackageOld = Nothing
End Sub
'------------- define Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim sConnect As String
Dim m_dbConnect As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim test As Variant
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.TransferObjectsTask2
'Opening the database
sConnect = "Provider=SQLOLEDB;Server=servername;Uid=sa;Pw d=;Database=source_db"
m_dbConnect.Open sConnect
'Opening a recordset
rs.Open "table_list", m_dbConnect
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask 4
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copy SQL Server Objects"
oCustomTask1.Description = "Copy SQL Server Objects"
oCustomTask1.SourceServer = "servername"
oCustomTask1.SourceUseTrustedConnection = True
oCustomTask1.SourceDatabase = "source_db"
oCustomTask1.DestinationServer = "servername"
oCustomTask1.DestinationUseTrustedConnection = True
oCustomTask1.DestinationDatabase = "destination_db"
oCustomTask1.ScriptFileDirectory = "C:Program FilesMicrosoft SQL Server80Tools"
oCustomTask1.CopyAllObjects = False
oCustomTask1.IncludeDependencies = True
oCustomTask1.IncludeLogins = False
oCustomTask1.IncludeUsers = False
oCustomTask1.DropDestinationObjectsFirst = True
oCustomTask1.CopySchema = True
oCustomTask1.CopyData = 1
oCustomTask1.ScriptOption = -2146995971
oCustomTask1.ScriptOptionEx = 17305616
oCustomTask1.SourceTranslateChar = True
oCustomTask1.DestTranslateChar = True
oCustomTask1.DestUseTransaction = False
oCustomTask1.UseCollation = False
'Looping through the recordset
rs.MoveFirst
Do Until rs.EOF
'Retrieving the table names
'test = rs!TABLE_NAME
oCustomTask1.AddObjectForTransfer rs!table_name, "dbo", 8
rs.MoveNext
Loop
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Can someone tell me what am I doing wrong here.
Thank you!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|