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...
Showing posts with label properties. Show all posts
Showing posts with label properties. Show all posts
Tuesday, March 27, 2012
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:
>|||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\Microso
ft%20SQL%20Server\80\Tools\Books\dts
prog.chm::/dtspcoll_7g6m.htm
"Andy S." <andymcdba1@.nospam.yahoo.com> wrote in message
news:andymcdba1@.nospam.yahoo.com:
> 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...
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:
>|||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\Microso
ft%20SQL%20Server\80\Tools\Books\dts
prog.chm::/dtspcoll_7g6m.htm
"Andy S." <andymcdba1@.nospam.yahoo.com> wrote in message
news:andymcdba1@.nospam.yahoo.com:
> 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...
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:
>|||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\Microso
ft%20SQL%20Server\80\Tools\Books\dts
prog.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...
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:
>|||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\Microso
ft%20SQL%20Server\80\Tools\Books\dts
prog.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...
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...
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...
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
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
Sunday, March 25, 2012
DTS problem: ActiveX Script Task Properties dialog box
My ActiveX Script Task Properties dialog box has the "functions" window
maximized and I
can no longer get at my code.
I can not find any way to resize it.
Please ...
If anyone has come across this and can help...
it will be much appreciated.
I've never had this happen before.
Thanks in advance,
bob mcclellanDid you find out the fix for that dialog box problem?|||I went to C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory
and Registered ALL dll's
*** Sent via Developersdex http://www.examnotes.net ***
maximized and I
can no longer get at my code.
I can not find any way to resize it.
Please ...
If anyone has come across this and can help...
it will be much appreciated.
I've never had this happen before.
Thanks in advance,
bob mcclellanDid you find out the fix for that dialog box problem?|||I went to C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory
and Registered ALL dll's
*** Sent via Developersdex http://www.examnotes.net ***
Wednesday, March 21, 2012
DTS Package with Dynamic Properties running Analysis Services Processing
Hi,
I wasn't sure of the location of this as it spans quite a few areas.
I have created a DTS package with Dynamic Parameters. The package is going to process the Dimensions of a cube therefore I have a dynamic property :
DimFolder -> String -> myServer\myCube\DimFolder
Which sets the TreeKey of the Analysis Services Processing Task to set the DimFolder to the correct server.
When I click on the parameters task and run is it works fine. If I then click on all the individual tasks within the DTS they all work fine as well.
However, when I click the play button to run the whole package is one go, I get
"Need to run the object to perform this operations. Code execution exception: EXCEPTION_ACCESS_VIOLATION".
I can't understand how this can happen as clicking each individual task and running each individual task works fine.
Any suggestions...
Jayne
Hi Jayne,
You may be encountering the problem described below, so try setting the Workflow Poperties of the Dynamic Properties task to "Execute on Main Thread":
http://support.microsoft.com/default.aspx?scid=kb;en-us;282966
>>
DTS Package Fails with Dynamic Properties Task and OLAP Processing Task
View products that this article applies to.
This article was previously published under Q282966
SYMPTOMS
When you process a Data Transformation Services (DTS) package that contains both a Dynamic Properties task and an OLAP Processing task, the DTS package may fail during execution of the Dynamic Properties task with the following error message:
1 task(s) failed during execution
When you double-click the failed Dynamic Properties Task, the following error dialog is displayed:Code execution exception: EXCEPTION_ACCESS_VIOLATION
CAUSE
The Dynamic Property task is used to set properties on other tasks, and the Analysis Services/OLAP processing task is a Single Threaded Apartment (STA) component. When the Dynamic Property task tries to set the properties on the STA based component, the Dynamic Property task must run on the main thread to set the properties of the STA based task.
>>
|||Also you may find that you have to use a scripting task rather than the dynamic properties task. Because of some binding issues I found when I was writing SSABI that I had to use a script task and walk the object model by-hand that the dynamic properties task returned errors , even when it was set to run on the main thread. You mileage may vary, but if the main thread setting doesn't work, try using a script task.
_-_-_ Dave
Sunday, March 11, 2012
DTS Package Error: Login failed for user sa
I am trying to edit a DTS package which is a transfer of data between two tables (connections)
i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa'
i am running enterprise manager on the server itself so i shouldn't have any connection issues.
if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:
' Cannot generate SSPI context 'Note: There are too many unknowns here to intelligently troubleshoot the situation:
S1 [I am trying to edit a DTS package which is a transfer of data between two tables (connections)]
Q1 Are both tables on the same server or on different servers?
S2 [i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa' i am running enterprise manager on the server itself so i shouldn't have any connection issues.]
Q2 i Have you tried entering the login password for sa
ii What is the result then?
iii Can you sucessfully login using say, Query Analyzer as sa?
S3 if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:' Cannot generate SSPI context '
Q3 Are your Sql Server(s) set to use integrated, standard, mixed, etc. authentication?
i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa'
i am running enterprise manager on the server itself so i shouldn't have any connection issues.
if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:
' Cannot generate SSPI context 'Note: There are too many unknowns here to intelligently troubleshoot the situation:
S1 [I am trying to edit a DTS package which is a transfer of data between two tables (connections)]
Q1 Are both tables on the same server or on different servers?
S2 [i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa' i am running enterprise manager on the server itself so i shouldn't have any connection issues.]
Q2 i Have you tried entering the login password for sa
ii What is the result then?
iii Can you sucessfully login using say, Query Analyzer as sa?
S3 if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:' Cannot generate SSPI context '
Q3 Are your Sql Server(s) set to use integrated, standard, mixed, etc. authentication?
Wednesday, March 7, 2012
DTS Package
Hi,
I create DTS Package, when I do the Transform Data Task Properties, the Table/View (Source Tab), and Table Name (Destination Tab), how can I see the whole table name? Especially, Table Name in Destination Tab, it show [database name].[dbo].[table name], I only see the 1st char of the table name in the dropdown list. Could you please let me know how can I resize the dropdownlist in order to see the table name?
Thanks.If you select that column on destination tab, using key board left arrow key you can move to the end of the value.
I create DTS Package, when I do the Transform Data Task Properties, the Table/View (Source Tab), and Table Name (Destination Tab), how can I see the whole table name? Especially, Table Name in Destination Tab, it show [database name].[dbo].[table name], I only see the 1st char of the table name in the dropdown list. Could you please let me know how can I resize the dropdownlist in order to see the table name?
Thanks.If you select that column on destination tab, using key board left arrow key you can move to the end of the value.
Tuesday, February 14, 2012
DTS Global Variables
Hello,
In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.
I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?
BrianAre you saying that you need to use this string variable in your WHERE clause? If so, you should be able to use a question mark (?) where you need to use that string value, then use the Parameters button to tell DTS which global variable to use for the parameter.
Terri|||Hey,
That was correct. THanks.
Brian|||Cool! I have found DTS to be one of the most powerful yet frustrating tools to use. Could they make it any more obtuse? Took me 2 years to realize there was such thing as global variables, which then opened up a whole new world.
Anyway, Marcin Policht has written a nice series on SQL Server 2000 DTS. There are 12 parts to it, and part 5 is:SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables. Definitely worth bookmarking.
Terri
Subscribe to:
Posts (Atom)