Hi,
does anyone have an example for doing a query in dts via vb an exporting it
to excel
ths and reg
bxandiUse the DTS Wizard to create a package then save the backage as a VB
file. You can then modify the VB code to your requirements.
An alternative option is to use Excel's QueryTables.Add method to
import the data with no DTS required.
David Portas
SQL Server MVP
--
Showing posts with label example. Show all posts
Showing posts with label example. 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(""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:
>|||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
DTS Programming Help
Hello,
I need to export all objects from a database to another database by programing in VC, but MSDN's example is in VB.
It is possible to use "TransferObjectsTask Object" in VC.But I don't know program step. Can you help me? thanks.Originally posted by n_fsk
Hello,
I need to export all objects from a database to another database by programing in VC, but MSDN's example is in VB.
It is possible to use "TransferObjectsTask Object" in VC.But I don't know program step. Can you help me? thanks.
you can make the DTS pacakge that does what u want (using Transfer Database task, etc.) and then save the DTS package as a VB file. You can then look at the code in VB for the DTS packages
I need to export all objects from a database to another database by programing in VC, but MSDN's example is in VB.
It is possible to use "TransferObjectsTask Object" in VC.But I don't know program step. Can you help me? thanks.Originally posted by n_fsk
Hello,
I need to export all objects from a database to another database by programing in VC, but MSDN's example is in VB.
It is possible to use "TransferObjectsTask Object" in VC.But I don't know program step. Can you help me? thanks.
you can make the DTS pacakge that does what u want (using Transfer Database task, etc.) and then save the DTS package as a VB file. You can then look at the code in VB for the DTS packages
Friday, March 9, 2012
DTS Package
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
ThanksYou need an ActiveX script task in your DTS package to do this. In your
ActiveX script task, you have to instantiate the FileSystemObject object to
rename the file.
Here's an example from one of my DTS packages:
dim DataFile, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DataFile = DTSGlobalVariables("FileLocation")
If FSO.FileExists(DataFile) Then
FSO.MoveFile DataFile, DTSGlobalVariables("ProcessedFiles") + "Orders_"
+CStr(Year(Now)) + Right("0" + CStr(Month(Now)), 2) + Right("0" +
CStr(Day(Now)), 2) + Right("0" + CStr(Hour(Now)), 2) + Right("0" +
CStr(Minute(Now)), 2) + Right("0" + CStr(Second(Now)), 2) + ".txt"
End If
Main = DTSTaskExecResult_Success
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Johnfli" <john@.ivhs.us> wrote in message
news:OFU2fLxiGHA.4204@.TK2MSFTNGP02.phx.gbl...
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
Thanks
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
ThanksYou need an ActiveX script task in your DTS package to do this. In your
ActiveX script task, you have to instantiate the FileSystemObject object to
rename the file.
Here's an example from one of my DTS packages:
dim DataFile, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DataFile = DTSGlobalVariables("FileLocation")
If FSO.FileExists(DataFile) Then
FSO.MoveFile DataFile, DTSGlobalVariables("ProcessedFiles") + "Orders_"
+CStr(Year(Now)) + Right("0" + CStr(Month(Now)), 2) + Right("0" +
CStr(Day(Now)), 2) + Right("0" + CStr(Hour(Now)), 2) + Right("0" +
CStr(Minute(Now)), 2) + Right("0" + CStr(Second(Now)), 2) + ".txt"
End If
Main = DTSTaskExecResult_Success
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Johnfli" <john@.ivhs.us> wrote in message
news:OFU2fLxiGHA.4204@.TK2MSFTNGP02.phx.gbl...
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
Thanks
Wednesday, March 7, 2012
DTS Package
Hi there
I've use an example I've found to build the following dts.execution from VB6:
Dim opackage As DTS.Package
Set opackage = New DTS.Package
opackage.LoadFromSQLServer PARS, , , DTSSQLStgFlag_UseTrustedConnection, , , , copyTxt, 0
opackage.Execute
opackage.UnInitialize
Set opackage = Nothing
The problem is when I execute it gives an error saying:
The specified DTS Package ('name='[notspecified]'; ID.Version ={'[notspecified]'},{'[notspecified]'}') does not exist.
Can anyone see what is wrong please? :eek:
ThanksWe had a similar problem here if the DTS package had more than one version. On the DTS packages, right click and go to versions. I would save the existing package under a new name, then delete all versions but one on the package referenced by the application.
I've use an example I've found to build the following dts.execution from VB6:
Dim opackage As DTS.Package
Set opackage = New DTS.Package
opackage.LoadFromSQLServer PARS, , , DTSSQLStgFlag_UseTrustedConnection, , , , copyTxt, 0
opackage.Execute
opackage.UnInitialize
Set opackage = Nothing
The problem is when I execute it gives an error saying:
The specified DTS Package ('name='[notspecified]'; ID.Version ={'[notspecified]'},{'[notspecified]'}') does not exist.
Can anyone see what is wrong please? :eek:
ThanksWe had a similar problem here if the DTS package had more than one version. On the DTS packages, right click and go to versions. I would save the existing package under a new name, then delete all versions but one on the package referenced by the application.
DTS Package
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
ThanksYou need an ActiveX script task in your DTS package to do this. In your
ActiveX script task, you have to instantiate the FileSystemObject object to
rename the file.
Here's an example from one of my DTS packages:
dim DataFile, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DataFile = DTSGlobalVariables("FileLocation")
If FSO.FileExists(DataFile) Then
FSO.MoveFile DataFile, DTSGlobalVariables("ProcessedFiles") + "Orders_"
+CStr(Year(Now)) + Right("0" + CStr(Month(Now)), 2) + Right("0" +
CStr(Day(Now)), 2) + Right("0" + CStr(Hour(Now)), 2) + Right("0" +
CStr(Minute(Now)), 2) + Right("0" + CStr(Second(Now)), 2) + ".txt"
End If
Main = DTSTaskExecResult_Success
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Johnfli" <john@.ivhs.us> wrote in message
news:OFU2fLxiGHA.4204@.TK2MSFTNGP02.phx.gbl...
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
Thanks
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
ThanksYou need an ActiveX script task in your DTS package to do this. In your
ActiveX script task, you have to instantiate the FileSystemObject object to
rename the file.
Here's an example from one of my DTS packages:
dim DataFile, FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DataFile = DTSGlobalVariables("FileLocation")
If FSO.FileExists(DataFile) Then
FSO.MoveFile DataFile, DTSGlobalVariables("ProcessedFiles") + "Orders_"
+CStr(Year(Now)) + Right("0" + CStr(Month(Now)), 2) + Right("0" +
CStr(Day(Now)), 2) + Right("0" + CStr(Hour(Now)), 2) + Right("0" +
CStr(Minute(Now)), 2) + Right("0" + CStr(Second(Now)), 2) + ".txt"
End If
Main = DTSTaskExecResult_Success
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Johnfli" <john@.ivhs.us> wrote in message
news:OFU2fLxiGHA.4204@.TK2MSFTNGP02.phx.gbl...
How can I have a DTS package rename a file?
for example, I have a file c:\temp.txt
I would like to have a DTS package change it to c:\temp060806.txt (the
numbers is teh date)
Thanks
dts only selected rows
I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis?
thanks...
kushpaw
You'll have to store the timestamp somewhere - perhaps in a file or a table, and then use a dynamic SQL statement using that timestamp.
-Jamie
|||
Perhaps, consider to use some sort of execution/audit tables and have your packages to get data extraction timeframesfrom there each time .
Rafael Salas
|||There is a timestamp field in both sql and as400 tables. I can select the last timestamp from the destination table and query source for that timestamp. Is that what u mean?
-kushpaw
|||kushpaw wrote:
There is a timestamp field in both sql and as400 tables. I can select the last timestamp from the destination table and query source for that timestamp. Is that what u mean?
-kushpaw
Yeah something like that. That would work.
|||thanks for your suggestion Rafael. this would be an ssis way, just what i was looking for.Sunday, February 19, 2012
DTS in Cluster environment!
Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
Tunji
You should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environmen
t).
|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
Tunji
You should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environmen
t).
|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).
DTS in Cluster environment!
Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
TunjiYou should have no problems. The DTS will exist inside the MSDB database so
it will be in both of the Clustered servers. The flat file you need to rea
d though needs to be in a stable location. (one that does not change owners
in the clustered environmen
t).|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
TunjiYou should have no problems. The DTS will exist inside the MSDB database so
it will be in both of the Clustered servers. The flat file you need to rea
d though needs to be in a stable location. (one that does not change owners
in the clustered environmen
t).|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).
DTS in Cluster environment!
Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
--
TunjiYou should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environment).|||Thanks Jeff!
--
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
--
TunjiYou should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environment).|||Thanks Jeff!
--
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).
Friday, February 17, 2012
DTS import/export Question
When importing a flat-text file, is it possible within the import/export wizard to tie more than one column as a DTSSource?
As an example, in an online store, we have columns from the flat-file like so:
"ID","Name","Description","Rarity","Price","Image","Weight"
And in the destination database, the columns are:
"ProductID","Name","Description","Price","Image","Weight"
I'd like to do the following within the dts wizard:
'********************************************************
Function Main()
DTSDestination("ProductID") = DTSSource("id")
DTSDestination("Name") = DTSSource("name")
DTSDestination("Description") = DTSSource("description") &"<br /><br />"& DTSSource("rarity")
DTSDestination("tImage") = DTSSource("image")
DTSDestination("Price") = DTSSource("Price")
DTSDestination("Weight") = DTSSource("Weight")
Main = DTSTransformStat_OK
End Function
'********************************************************
Obviously the description line errors out. Any suggestion on how to accomplish this for an import?You could import 'rarity' as another column, then combine the two in your SELECT query:
SELECT (description + '<br /><br />' + rarity) AS description ...Or import the rarity as a column and then run SQL to combine the two
UPDATE mytable SET description = (description + '<br /><br />' + rarity)...of course you would have to provide logic to not combine previously combined columns. I realize both of these options contain reduntant data, I guess you could add a temp column, import,combine,delete temp column.
Tuesday, February 14, 2012
DTS Help with Update?
I'd like to create a DTS package to update records within a table
leaving other information stored in those records as is. For example,
table a has a record with 5 columns where 3 are null. An external
source has the non-null values for those 3 columns. Can anyone help
with some general steps as to how I might fill those 3 columns in the
sql table?
leaving other information stored in those records as is. For example,
table a has a record with 5 columns where 3 are null. An external
source has the non-null values for those 3 columns. Can anyone help
with some general steps as to how I might fill those 3 columns in the
sql table?
ThanksIf the source is the text file. u must first import text source into
table.
then exec update statement in sql query task.
Subscribe to:
Posts (Atom)