I am trying to write a Windows Script file that will update an exisiting DTS
package. The objective is to add an

additional mapping to the single transformation in a DataPump task. The
existing transformation maps many fields,

and I'm just trying to add a field to the list. The code I'm trying to use
is pasted below. I get an error when

trying to specify the transformation.

Thanks for your help!

~ Craig Stewart

************************************************** **

Sub UpdateDTSPackage(sServerName)
Dim dtsPackage
Dim oTask
Dim oTransformation
Dim oColumn

Set dtsPackage = WScript.CreateObject("DTS.Package2")
Call dtsPackage.LoadFromSQLServer(sServerName, "", "",
DTSSQLStgFlag_UseTrustedConnection, "", "", "",

"LS_Load_CLX_20061117_tmp")

Set oTask = dtsPackage.Tasks("DTSTask_DTSDataPumpTask_1")
Set oTransformation= oTask.Transformations("DTSTransformation__1")

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''
' ERROR TEXT BELOW OCCURS FOLLOWING THE LINE ABOVE

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''
' c:\Documents and Settings\cas0050\Desktop\New Folder\Version
2.7\dts\updatedts.w
' sf(68, 5) Microsoft VBScript runtime error: Object doesn't support
this property
' or method: 'oTask.Transformations'

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''

Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Col098" , 1)
oColumn.Name = "Col098"
oColumn.Ordinal = 1
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn

Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("RenewalBoo kedDate" , 1)
oColumn.Name = "RenewalBookedDate"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn

Set oColumn = Nothing
Set oTransformation = Nothing
Set oTask = Nothing

Call dtsPackage.SaveToSQLServer(sServerName, "", "",
DTSSQLStgFlag_UseTrustedConnection)

Set dtsPackage = Nothing
End Sub

Hello Craig,

Transformations are on the CustomTask and not on the task. So you may want
something like

Dim oCustomTask As DTS.DataPumpTask2

Set oCustomTask = oTask.CustomTask

The easiest way to have a look and see where it may be going wrong is to
save the package out as a VB file. In there you will see how the package
constructs itself.


Regards

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

Thanks so much.

Changing:
Set oTransformation= oTask.Transformations("DTSTransformation__1")

to:
Set oTransformation= oTask.CustomTask.Transformations("DTSTransformatio n__1")

resolved my problem.

"Allan Mitchell" wrote: