HI All,

I am having difficulty trying to create a dts package that will allow me to change the table source and destination source.

My source is dbf's with memo fields and my destination tables is SQL 2000. Assuming a one-to-one correspondence in fields/columns with no transformation issues how do I create a DTS package that will allow me to use activeX scripting to change source and destination table so that DTS allows me to import VFP tables into SQL.

I can create a dts package using dts designer but it defaults to the tables that was chosen in the designer.

Here is my sample script that I tried to run but when I run it, it does not change the source and destination tables. This was defined with ODBC dsn to VFP tables.

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


I know that it can be done, but can someone help me? I need to be able to import dbf (with memo fields) into SQL.

Thanks,
Joy