Need to code a DTS (generic) template to do the following…

1. I have 100+ dbf tables some with memo fields
2. Assuming a one-to-one correspondence with dbf (vfp tables) and sql tables (already created)
3. How do I modify a dts package to handle data transfer from vfp to sql
I pass to dts, sourcetable and target table using global variables
4. I want to set the source and target and let dts handle transformation

My vb script

Option Explicit

Function Main()
Dim cSourcevar, sSqlStatement, oPkg, oDataPump, sDestinationTable

‘read global variables
cSourcevar = DTSGlobalVariables("sourcedbf").value
sDestinationTable = DTSGlobalVariables("targettable").value

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

oDataPump.SourceObjectName = cSourcevar
oDataPump.DestinationObjectName = sDestinationTable

Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSStepScriptResult_ExecuteTask
End Function


It seems that the data transformation column is taking the original definitions from what I defined first when I created the package. How do I programmatically change that?

I’ve been searching for the answers, hope you can help.

Thanks,
Joy