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&#3 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!