DTS Programming Question -- how to remove custom transformations

In SQL Server Books Online, there is a great programming example where you
can modify the properties of a data pump task to add new transformations.
My question is how do you remove them when done? Those transformations stay
saved so you cannot recreate them later. Here is the code I am using from
the "DTS Example: Running Concurrent Operations in Visual Basic" topic, I
want to remove the transformation named "CopyNorthwindProducts" after I'm
done using it, preferably via an ActiveX script in DTS. that would follow
the pump task. Thanks for your help.
--Andy S.
andymcdba1@.nomorespam.yahoo.com
Please remove nomorespam before replying.
'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " &
vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" &
vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTaskYou want to remove a transformation object?
Can you not use something like
for each tform in dpump.Transformations
dpump.Transformations.Remove tform.Name
next
"Andy S." <andymcdba1@.nospam.yahoo.com> wrote in message
news:andymcdba1@.nospam.yahoo.com:
> In SQL Server Books Online, there is a great programming example where you
> can modify the properties of a data pump task to add new transformations.
> My question is how do you remove them when done? Those transformations
> stay
> saved so you cannot recreate them later. Here is the code I am using from
> the "DTS Example: Running Concurrent Operations in Visual Basic" topic, I
> want to remove the transformation named "CopyNorthwindProducts" after I'm
> done using it, preferably via an ActiveX script in DTS. that would follow
> the pump task. Thanks for your help.
> --Andy S.
> andymcdba1@.nomorespam.yahoo.com
> Please remove nomorespam before replying.
>
> 'Create transform to copy row, signal completion.
> Set objTransform = objPumpTask.Transformations. _
> New("DTSPump.DataPumpTransformScript")
> With objTransform
> .Name = "CopyNorthwindProducts"
> .TransformPhases = DTSTransformPhase_Transform + _
> DTSTransformPhase_OnPumpComplete
> Set objTranScript = .TransformServer
> End With
> With objTranScript
> .FunctionEntry = "CopyColumns"
> .PumpCompleteFunctionEntry = "PumpComplete"
> .Language = "VBScript"
> sVBS = "Option Explicit" & vbCrLf
> sVBS = sVBS & "Function CopyColumns()" & vbCrLf
> sVBS = sVBS & " DTSDestination(""ProductName"") => DTSSource(""ProductName"") " & vbCrLf
> sVBS = sVBS & " DTSDestination(""CategoryName"") => DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
> sVBS = sVBS & " DTSDestination(""CompanyName"") => DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " &
> vbCrLf
> sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") => CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
> sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
> sVBS = sVBS & "End Function" & vbCrLf
> sVBS = sVBS & "Function PumpComplete()" & vbCrLf
> sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" &
> vbCrLf
> sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
> sVBS = sVBS & "End Function" & vbCrLf
> .Text = sVBS
> End With
> objPumpTask.Transformations.Add objTransform
> objPackage.Tasks.Add objTask|||Thank you! That works great. If I may ask, how did you learn that/find it
out?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OjHvf6gDFHA.628@.TK2MSFTNGP15.phx.gbl...
> You want to remove a transformation object?
> Can you not use something like
> for each tform in dpump.Transformations
> dpump.Transformations.Remove tform.Name
> next
>
> "Andy S." <andymcdba1@.nospam.yahoo.com> wrote in message
> news:andymcdba1@.nospam.yahoo.com:
>> In SQL Server Books Online, there is a great programming example where
>> you
>> can modify the properties of a data pump task to add new transformations.
>> My question is how do you remove them when done? Those transformations
>> stay
>> saved so you cannot recreate them later. Here is the code I am using
>> from
>> the "DTS Example: Running Concurrent Operations in Visual Basic" topic, I
>> want to remove the transformation named "CopyNorthwindProducts" after I'm
>> done using it, preferably via an ActiveX script in DTS. that would follow
>> the pump task. Thanks for your help.
>> --Andy S.
>> andymcdba1@.nomorespam.yahoo.com
>> Please remove nomorespam before replying.
>>
>> 'Create transform to copy row, signal completion.
>> Set objTransform = objPumpTask.Transformations. _
>> New("DTSPump.DataPumpTransformScript")
>> With objTransform
>> .Name = "CopyNorthwindProducts"
>> .TransformPhases = DTSTransformPhase_Transform + _
>> DTSTransformPhase_OnPumpComplete
>> Set objTranScript = .TransformServer
>> End With
>> With objTranScript
>> .FunctionEntry = "CopyColumns"
>> .PumpCompleteFunctionEntry = "PumpComplete"
>> .Language = "VBScript"
>> sVBS = "Option Explicit" & vbCrLf
>> sVBS = sVBS & "Function CopyColumns()" & vbCrLf
>> sVBS = sVBS & " DTSDestination(""ProductName"") =>> DTSSource(""ProductName"") " & vbCrLf
>> sVBS = sVBS & " DTSDestination(""CategoryName"") =>> DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
>> sVBS = sVBS & " DTSDestination(""CompanyName"") =>> DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " &
>> vbCrLf
>> sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") =>> CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
>> sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
>> sVBS = sVBS & "End Function" & vbCrLf
>> sVBS = sVBS & "Function PumpComplete()" & vbCrLf
>> sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" &
>> vbCrLf
>> sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
>> sVBS = sVBS & "End Function" & vbCrLf
>> .Text = sVBS
>> End With
>> objPumpTask.Transformations.Add objTransform
>> objPackage.Tasks.Add objTask
>sqlsql