Hello All,

I come from a DBA back ground. So, I'm a new to DTS programming and VBScript
programming though I'm reasonably familiar with the DTS UI.

I was going through Ken Henderson's book and Wrox book in the hope of
learning DTS programming. I was going thru the "Parameterized DTS Package"
in his book and I'm trying to use the same concept to transfer a table from
a source table to a destination table.

I create two GlobalVariables, one for the Source table - gSourceTableName
and one for the Destination Table - gDestinationTableName. I assigned
Northwind..Orders to the source and junk..Orders to the destination. But
when I run the package it gives the error "Invalid Object name :
Junk..Orders". I found out why as the Orders table was not created at the
destination db (Junk in this case). But when I create the table, it gives
errors for the columns which it assumes by default when the package is
created.

I have attached the screen dump and the ActiveX Script which assigns the
Global Variables to the Source and Destination tables.

Can you anyone please help me in the direction.


ActiveX Script Task Properties : AssignGlobalVariables

'************************************************* *******' Visual Basic
ActiveX Script
'************************************************* *******'

Function Main()

Dim oPkg
Dim oTask
Set oPkg = DTSGlobalVariables.Parent
Set oTask = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").Customtask
oTask.SourceObjectName = DTSGlobalVariables("gSourceTableName").value
oTask.DestinationObjectName =
DTSGlobalVariables("gDestinationTableName").value

' msgbox "Source table is : " & DTSGlobalVariables("gSourceTableName").value
' msgbox "Destination table is : " &
DTSGlobalVariables("gDestinationTableName").value

Main = DTSTaskExecResult_Success
End Function


Thanks,
Gopi

P.S : I tried sending in the attachment as well but it failed to go through
though the attachment is only 103kb. If anyone wants to look at the screen
dump of the dts designer please send me a mail (rgopinath (AT) hotmail (DOT) com)

I figured out that I have to specify the column mappings too in the
transformation phase. However, I dont know how/where to specify in the
script that the transformation should also copy the Source Table structure
to the Destination table structure. Can someone please help me out ?

Gopi

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

If DTSGlobalVariables("gSourceTableName").Value = "Region" then
DTSDestination("RegionID") = DTSSource("RegionID")
DTSDestination("RegionDescription") = DTSSource("RegionDescription")
End If

Main = DTSTransformStat_OK

End Function


"rgn" <rgn (AT) discussions (DOT) microsoft.com> wrote

If you send me a mail I have a lot of code to do this

--


Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com



"gopi" <rgopinath (AT) hotmail (DOT) com> wrote