DTS Script trying to re-execute another task in package

I'm new to the whole DTS scene and not impressed with the info I'm finding. Here's what I'm trying to do:

I have two recordsets generated by ExecteSQLTasks (created as output global variables). While I'm looping through one recordset in a script I would like to do data-driven updates of the second recordset based on data in the first. This seemed easy at first. I thought I could just do a little hoochie coochie:

Dim objPackage
Dim objTask
Dim objCustomTask

Set objPackage = DTSGlobalVariables.Parent

For each objTask in objPackage.Tasks
If objTask.CustomTask.Description = "The description I gave it!" Then
objTask.Properties("SQLStatement").Value = "My new SQL statement"
objTask.Execute
End If

And voila! I'd have my new recordset established as a new value for the global variable. But no! It doesn't seem to work that way. What's up with this?

FYI, I also tried to apply filters to the original recordset using:

rs.Filter = " table.field = " & varMyDataNotAString

But that didn't work either. Grrrr!

Anybody out there doing things like this and getting them to work?

Thanks!Check SQLDTS (http://www.sqldts.com) website and search for GLOBAL VARIABLES which will show code examples to use so.

HTH|||Actually, I'm pretty sure this is where I originally got the code in my initial post. The issue is that using this code did not refresh my global variable (and hence my recordset). I could change the SQL statement, but either the Task doesn't actually execute with the

objTask.CustomTask.Execute

or somehow the global variable isn't refreshed.

I plan to continue playing with it today.

Thanks for your reply!

DTS Scheduled Task Fails - Permission Denied

I searched the forum's threads on this, and while there were many results, none have helped so far.

I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.

Function Main()

Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")

FSO.CopyFile "\\<server>\<Share>\Data.mdb", "\\<server>\C$\DataCopy\Data.mdb"

Main = DTSTaskExecResult_Success
End Function

The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(

I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.

My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.

The Scheduled Task fails with the common error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.

I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)

I am at a loss here. Thanks for your help.I'll bump this once.|||I'm still working on it so not sure if what I suspect is the issue... but have a dts package that download a file, based on SQL statement from as/400 to sql table... runs great when I run it myself within the enterprise manager but when I try to call from a stored procedure via my exe it fails to run.

What I've deduced is that when I run it within the enterprise manager that it is, in essence, running on the client... locally and using my dll's, etc... however, when I run via stored procedure through my exe it is trying to run on the server, herein lies the problem (really strong guess at this point)... the server is not set up to connect to the as/400 and that's where it bombs.

I'm guessing that your sql server is having issues talking to your access database in much the same way that mine is having issues talking to the as/400. I've got the client software installed now and will be testing my theory shortly. Hope this helps you with your issue.

b|||Is the access db on the same server as Sql server?

If so, do non unc paths work? Such as :

FSO.CopyFile "c:\somefolder\Data.mdb", "c\DataCopy\Data.mdb"|||.. I have a package as well.
The thing is that if I put in a msgbox to execute directly before the ".deletefile", the package will run, but if I outcomment It it fails...
I can on the other hand do an ".copyfile" instead...
I only have the problem with the ".deletefile"
I would also appriciate all help i can get on this.

// selander

Function Main()

Dim Eso, E, E1, Ec
Set Eso = CreateObject("Scripting.FileSystemObject")
Set E = Eso.GetFolder("\\CLUST\SYS\IMPORT\ENT\")
Set Ec = E.Files
For Each E1 in Ec
if ucase(E1.name) = ucase(DTSGlobalVariables("strFileName").Value)+".ZIP" then
msgbox "FUBAR"
Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
exit for
end if
Next
Main = DTSTaskExecResult_Success
End Functionsqlsql

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 question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...
Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison
sqlsql

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison

DTS question

Is there a way to use Global variables in the 'Execute SQL task' in a DTS package? if no is there a way to?Yes, depends on what version of SQL Server you have. In SQL Server 2000, you can use question marks as place holders for parameters, then assign the global variables to the question marks using the parameters button in the execute sql screen.

If you are using sql7 then I found this to work. Add an active x script to the package that creates and sets the sql command of the execute sql task. when you create the sql command in the vb script, use the variables you want. Here is the syntax of the active x script.

Steve

'*********************************
' Visual Basic ActiveX Script
'*********************************

Function Main()

Dim oPkg, oExecSQL, sSQLStatement

sSQLStatement = "EXEC stpr_createtable " & DTSGlobalVariables("tableName").Value

Set oPkg = DTSGlobalVariables.Parent
Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

oExecSQL.SQLStatement = sSQLStatement

Set oExecSQL = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

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 problem: ActiveX Script Task Properties dialog box

My ActiveX Script Task Properties dialog box has the "functions" window
maximized and I
can no longer get at my code.
I can not find any way to resize it.
Please ...
If anyone has come across this and can help...
it will be much appreciated.
I've never had this happen before.
Thanks in advance,
bob mcclellanDid you find out the fix for that dialog box problem?|||I went to C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory
and Registered ALL dll's
*** Sent via Developersdex http://www.examnotes.net ***

DTS Problem

Hi,
Can any one tell me how can i use and from where i can study about
execute sql task in dts.
I have a problem that i have serverA and a OLAP Server same as my
server A.
I had scheduled a job to transfer data on nighlty basis.Some time my
team memeber(Programmer) adds a field in a table on server A . When my
job executes on olap server it gives error and fails.
The error occurs because of column not found in Table of OLAP Server.
To remove the error i have to manually increase the field in that
table.
Later on i use drop table and select * into table name from
database.username.tablename
But i think this is not a best way to do.
Can any one suggest me what should i do in this case.
from
killerHi
There is good documentation on DTS in Books online, which should be in your
SQL Server programs group on the start menu. You may want to check you have
the latest version or download it from
http://www.microsoft.com/sql/techin...2000/books.mspx
There is also a very good site at www.sqldts.com which has many examples and
articles.
It is not a good idea to use SELECT * in production code as you it can break
(as yours does) if some changes the columns.
John
"doller" wrote:

> Hi,
> Can any one tell me how can i use and from where i can study about
> execute sql task in dts.
> I have a problem that i have serverA and a OLAP Server same as my
> server A.
> I had scheduled a job to transfer data on nighlty basis.Some time my
> team memeber(Programmer) adds a field in a table on server A . When my
> job executes on olap server it gives error and fails.
> The error occurs because of column not found in Table of OLAP Server.
> To remove the error i have to manually increase the field in that
> table.
> Later on i use drop table and select * into table name from
> database.username.tablename
> But i think this is not a best way to do.
> Can any one suggest me what should i do in this case.
> from
> killer
>|||So, What should be done so that i can come to know that i have to
increase the field or how can i automaticaly schedule it.
from
killer
John Bell wrote:[vbcol=seagreen]
> Hi
> There is good documentation on DTS in Books online, which should be in you
r
> SQL Server programs group on the start menu. You may want to check you hav
e
> the latest version or download it from
> http://www.microsoft.com/sql/techin...2000/books.mspx
> There is also a very good site at www.sqldts.com which has many examples a
nd
> articles.
> It is not a good idea to use SELECT * in production code as you it can bre
ak
> (as yours does) if some changes the columns.
> John
> "doller" wrote:
>|||Hi
This is a code management issue. Making the columns will mean it won't
break, but if you are adding the extra column you should know from the chang
e
analysis that you are going to need to change the job to populate the table,
adding the extra column when it was not needed would not help performance.
John
"doller" wrote:

> So, What should be done so that i can come to know that i have to
> increase the field or how can i automaticaly schedule it.
> from
> killer
>
> John Bell wrote:
>

DTS Problem

Hi,
Can any one tell me how can i use and from where i can study about
execute sql task in dts.
I have a problem that i have serverA and a OLAP Server same as my
server A.
I had scheduled a job to transfer data on nighlty basis.Some time my
team memeber(Programmer) adds a field in a table on server A . When my
job executes on olap server it gives error and fails.
The error occurs because of column not found in Table of OLAP Server.
To remove the error i have to manually increase the field in that
table.
Later on i use drop table and select * into table name from
database.username.tablename
But i think this is not a best way to do.
Can any one suggest me what should i do in this case.
from
killer
Hi
There is good documentation on DTS in Books online, which should be in your
SQL Server programs group on the start menu. You may want to check you have
the latest version or download it from
http://www.microsoft.com/sql/techinf...000/books.mspx
There is also a very good site at www.sqldts.com which has many examples and
articles.
It is not a good idea to use SELECT * in production code as you it can break
(as yours does) if some changes the columns.
John
"doller" wrote:

> Hi,
> Can any one tell me how can i use and from where i can study about
> execute sql task in dts.
> I have a problem that i have serverA and a OLAP Server same as my
> server A.
> I had scheduled a job to transfer data on nighlty basis.Some time my
> team memeber(Programmer) adds a field in a table on server A . When my
> job executes on olap server it gives error and fails.
> The error occurs because of column not found in Table of OLAP Server.
> To remove the error i have to manually increase the field in that
> table.
> Later on i use drop table and select * into table name from
> database.username.tablename
> But i think this is not a best way to do.
> Can any one suggest me what should i do in this case.
> from
> killer
>
|||So, What should be done so that i can come to know that i have to
increase the field or how can i automaticaly schedule it.
from
killer
John Bell wrote:[vbcol=seagreen]
> Hi
> There is good documentation on DTS in Books online, which should be in your
> SQL Server programs group on the start menu. You may want to check you have
> the latest version or download it from
> http://www.microsoft.com/sql/techinf...000/books.mspx
> There is also a very good site at www.sqldts.com which has many examples and
> articles.
> It is not a good idea to use SELECT * in production code as you it can break
> (as yours does) if some changes the columns.
> John
> "doller" wrote:
|||Hi
This is a code management issue. Making the columns will mean it won't
break, but if you are adding the extra column you should know from the change
analysis that you are going to need to change the job to populate the table,
adding the extra column when it was not needed would not help performance.
John
"doller" wrote:

> So, What should be done so that i can come to know that i have to
> increase the field or how can i automaticaly schedule it.
> from
> killer
>
> John Bell wrote:
>

DTS Problem

Hi,
Can any one tell me how can i use and from where i can study about
execute sql task in dts.
I have a problem that i have serverA and a OLAP Server same as my
server A.
I had scheduled a job to transfer data on nighlty basis.Some time my
team memeber(Programmer) adds a field in a table on server A . When my
job executes on olap server it gives error and fails.
The error occurs because of column not found in Table of OLAP Server.
To remove the error i have to manually increase the field in that
table.
Later on i use drop table and select * into table name from
database.username.tablename
But i think this is not a best way to do.
Can any one suggest me what should i do in this case.
from
killerHi
There is good documentation on DTS in Books online, which should be in your
SQL Server programs group on the start menu. You may want to check you have
the latest version or download it from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
There is also a very good site at www.sqldts.com which has many examples and
articles.
It is not a good idea to use SELECT * in production code as you it can break
(as yours does) if some changes the columns.
John
"doller" wrote:
> Hi,
> Can any one tell me how can i use and from where i can study about
> execute sql task in dts.
> I have a problem that i have serverA and a OLAP Server same as my
> server A.
> I had scheduled a job to transfer data on nighlty basis.Some time my
> team memeber(Programmer) adds a field in a table on server A . When my
> job executes on olap server it gives error and fails.
> The error occurs because of column not found in Table of OLAP Server.
> To remove the error i have to manually increase the field in that
> table.
> Later on i use drop table and select * into table name from
> database.username.tablename
> But i think this is not a best way to do.
> Can any one suggest me what should i do in this case.
> from
> killer
>|||So, What should be done so that i can come to know that i have to
increase the field or how can i automaticaly schedule it.
from
killer
John Bell wrote:
> Hi
> There is good documentation on DTS in Books online, which should be in your
> SQL Server programs group on the start menu. You may want to check you have
> the latest version or download it from
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
> There is also a very good site at www.sqldts.com which has many examples and
> articles.
> It is not a good idea to use SELECT * in production code as you it can break
> (as yours does) if some changes the columns.
> John
> "doller" wrote:
> > Hi,
> > Can any one tell me how can i use and from where i can study about
> > execute sql task in dts.
> >
> > I have a problem that i have serverA and a OLAP Server same as my
> > server A.
> > I had scheduled a job to transfer data on nighlty basis.Some time my
> > team memeber(Programmer) adds a field in a table on server A . When my
> > job executes on olap server it gives error and fails.
> > The error occurs because of column not found in Table of OLAP Server.
> > To remove the error i have to manually increase the field in that
> > table.
> > Later on i use drop table and select * into table name from
> > database.username.tablename
> > But i think this is not a best way to do.
> >
> > Can any one suggest me what should i do in this case.
> >
> > from
> > killer
> >
> >|||Hi
This is a code management issue. Making the columns will mean it won't
break, but if you are adding the extra column you should know from the change
analysis that you are going to need to change the job to populate the table,
adding the extra column when it was not needed would not help performance.
John
"doller" wrote:
> So, What should be done so that i can come to know that i have to
> increase the field or how can i automaticaly schedule it.
> from
> killer
>
> John Bell wrote:
> > Hi
> >
> > There is good documentation on DTS in Books online, which should be in your
> > SQL Server programs group on the start menu. You may want to check you have
> > the latest version or download it from
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
> >
> > There is also a very good site at www.sqldts.com which has many examples and
> > articles.
> >
> > It is not a good idea to use SELECT * in production code as you it can break
> > (as yours does) if some changes the columns.
> >
> > John
> >
> > "doller" wrote:
> >
> > > Hi,
> > > Can any one tell me how can i use and from where i can study about
> > > execute sql task in dts.
> > >
> > > I have a problem that i have serverA and a OLAP Server same as my
> > > server A.
> > > I had scheduled a job to transfer data on nighlty basis.Some time my
> > > team memeber(Programmer) adds a field in a table on server A . When my
> > > job executes on olap server it gives error and fails.
> > > The error occurs because of column not found in Table of OLAP Server.
> > > To remove the error i have to manually increase the field in that
> > > table.
> > > Later on i use drop table and select * into table name from
> > > database.username.tablename
> > > But i think this is not a best way to do.
> > >
> > > Can any one suggest me what should i do in this case.
> > >
> > > from
> > > killer
> > >
> > >
>

dts parameters, more values in one parameter

hy all,

i'm using the object transform data task from a server (where i'm not dbo) to another server (where i'm dbo).

i'm getting data from a table. i need to select just some records from this table. i need to use a query like this one below...

select * from {table_name} where operationedate in ('20070101', '20070205', '20060524')

... cause everytime i run the dts the operationdate field must be filtered on different date.

so i tried to use the parameter from a global variables. i've tried lots of things but everytime i failed.

i used to try:

select * from {table_name} where operationedate in (?)

but it doesnt work!

any one can understand what i try to explain and even help me?

bye

nicola

Have you tried using a SSIS variable with an expression to build query dinamically?

There are a lot of post about it. You would need the set the EvaluateAsExpression porperty of the variable as TRUE and then build the expression. The expression will use another variable that has the coma delimited list of values with the dates you care about.

|||

Build two variables: varInClause (string) & varSQLStatement (string)

For the value of varInClause, you can type your dates including the quotes and commas: '20070101','20070205',...

Set EvaluateAsExpresstion to true on the varSQLStatement variable. In the expressions box, type this as your expression:

"select * from table_name where operationdate in (" + @.{User::varInClause} + ")"

Then, use the variable, varSQLStatement, in your OLE DB source.

|||

thank you very much for answered so soon

but maybe i'm still in trouble

i forgot you to say i'm working in SQL Server 2000

i think that is possible to use SSIS only in SQL Server 2005

i'm right or not?

bye

nicola

|||Oh, well, that is a very important piece of information because this is not a DTS forum. If you're working with DTS, you need to post that question on the DTS newsgroup.

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

thank you very much

i'm sorry for time you loose with my problem

bye

nicola

DTS Package troubles

This summary is not available. Please click here to view the post.

Dts package that imports difference

Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>
in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||Hi
"italic" wrote:

> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John
|||Hi
"italic" wrote:

> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John
sqlsql

DTS Package Error

Hi,

I have a DTS Package which creates a text file and then sends that file as an attachment in email using "send mail task".

I can run this package manually but if I schedule this package, its getting failed. Here is the error message

"DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSSendMailTask_1 DTSRun OnError: DTSStep_DTSSendMailTask_1, Error = -2147220352 (80040480) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220352 (80040480); Provider Error: 0 (0) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSSendMailTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed."

Could you please help me with this issue? I am using SQL Server 2000.

Thanks!Have you enabled the ability to send mail ?|||I enabled mail, but now I am getting different error message

"DTSStep_DTSSendMailTask_2, Error = -2147220352 (80040480) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) P. The step failed."

Thanks!|||Is sql server agent running ? And have you setup the mail profile within the agent ?