I use ActiveX script for data transformation. The source table and the
destination table are exactly the same. For a table that has two
columns, the following function works.

-------------
Function Main()
DTSDestination(1) = DTSSource(1)
DTSDestination(2) = DTSSource(2)

Main = DTSTransformStat_OK
End Function
-------------

The problem is, I don't know how many columns are there in a source
table (because I am looping through a list of tables).

??? How can I find out how many columns are there is a source table ????

For your info, I have tried the following and it doesn't work. The error
is like: DTSSource is not a valid object or function.

-----------------
Function Main()
dim n,i
n = 100
for i=1 to n
'msgbox("source_column1, name -" & DTSSource(1).name)
'msgbox("source_column1, value -" & DTSSource(1).value)

if DTSSource(i).name<>"" and not isNull(DTSSource(i).name) then
DTSDestination(i) = DTSSource(i)
else
msgBox("total columns :" & i-1)
exit for
end if
next

Main = DTSTransformStat_OK
End Function
---------------

Hello q_test,


The SourceColumns property of a transform determine what can be used in a
transformation. How do you determine how many columns you have? You could
issue a query and parse the results. Another option would be to parse the
metadata tables from the source provider. In SQL Server you could issue
a query against the INFORMATION_SCHEMA.COLUMNS table. You would need to
dynamically build the transform each time.


Allan