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(""Cat egoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") =
DTSLookups(""SupplierLU"").Execute(DTSSource(""Sup plierID"").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
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(""Cat egoryID"")) " & vbCrLf
> sVBS = sVBS & " DTSDestination(""CompanyName"") =
> DTSLookups(""SupplierLU"").Execute(DTSSource(""Sup plierID"").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:
>
|||I had the need one day to build a package which took a text Query , Any
Query, parse it, Build a table in Excel, destroy everything in my
DataPump task, rebuild it (Source SQL Statements, Source Columns,
Destination Columns, Destination Objects, Transformations) and do it all
dynamically from within the package itself.
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\dtsprog.chm::/dtspcoll_7g6m.htm
"Andy S." <andymcdba1@.nospam.yahoo.com> wrote in message
news:andymcdba1@.nospam.yahoo.com:[vbcol=seagreen]
> 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...