dts question, data pump task hangs when i try to edit it!

I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.

Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4

Quote:

Originally Posted by catstevens

I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.

Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4

Have you connected to your data source before doing this?|||hi,

i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.

it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).

thanks|||

Quote:

Originally Posted by catstevens

hi,

i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.

it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).

thanks

Hey dude,

What do you mean by editing on the server. I am a newbie to the data world and am trying to do the same ..extracting the data from TeraData to SQL Server. Its hanging and I was never able to get this done.

Can you please help me here...

THanks,
Rahul

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...

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...

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...

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...

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

DTS Package Execution Order

I have a package that has 12 data pump tasks all executing in parallel.

It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.

Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.

This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.

Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.

I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.

Any ideas would be much appreciated.Darren Green suggested
Why not take a simper approach and only populate your progress list as
tasks start executing. You could drive this quite happily off events.

To determine order of execution you would need to enumerate all steps as
constraints are held by the task they go to, not from.

For each step, enumerate the PrecedenceConstraints collection, to get
the PrecedenceConstraint objects. The StepName is the preceding step, So
if a step as no PrecedenceConstraints it is the start step. Not sure
that this guaranteed to 100 accurate either as in theory you can change
the basis and result to in effect be a "On Preceeding Step Not Run", and
have a circular reference, but I suspect DTS itself may have the same
problem as you in this case, so probably not worth worrying about for
the start step, but perfectly valid elsewhere.

Dts Package Error

I am importing a text file into sql table through DTS Package
i am getting an error:
Microsoft DATA Transformation services {DTS} Data Pump
The number of failing rows exceeds the maximum specified.
TrasformCopy 'DTS Transformation_95' Conversion error:
Destination overflowed on column pair 1 (source column 'Col95'(DBTYPE_STR), destination column 'WGT01' (DBTYPE_NUMERIC)).

please help i am new to DTS PACHAGE.Check the collation settings for this table and target table too, see any difference. Refer thru Job steps from History.

DTS Insert or Update

Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.

I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.

i think your going to have to use VBScript in the transformation step of the DTS Package to evaluate if the record exists. Similar to what one would do in the stored proc.

hth,

mcm