May 31, 2002 at 2:44 pm
How do you call and run a DTS from within another DTS? I'd like to be able to create a DTS for each step of a process, and then run each DTS in sequence.
For example, my first DTS would extract data.
The next DTS would scrub the data.
The last DTS would run some analysis on the data.
I would like to run each DTS to perform this process similar to calling each as a subroutine...
Call DTS1
Call DTS2
Call DTS3
Additionally, if this is possible, how might I pass parameters to each DTS?
May 31, 2002 at 6:17 pm
In SQL 2000, there is an execute package task that does this.
In SQL 7, you can do it with ActiveX
Steve Jones
June 3, 2002 at 7:37 am
I'm using SQL Server 7. Can you be a little more specific as to the syntax of calling a DTS from within an ActiveX Script?
Thanks!!!
June 3, 2002 at 6:56 pm
Here's an edited sample from one of my scripts:
Dim objDTSPackage
Set objDTSPackage = CreateObject("DTS.Package2")
objDTSPackage.LoadFromSQLServer "SQLServer", , , DTSSQLStgFlag_UseTrustedConnection, _
, , , "DTS Package Name"
objDTSPackage.GlobalVariables("GlobalVariableName") = SomeValue
objDTSPackage.Execute
NewValue = objDTSPackage.GlobalVariables("GlobalVariableName2")
Set objDTSPackage = Nothing
I believe that the Package2 object was available with SQL Server 7, but if you don't have that, you can just change "DTS.Package2" to "DTS.Package". Both should work here. If you don't use NT/2000 authentication to connect to your SQL server, you can look in BOL for the parameters for the LoadFromSQLServer method. To find it, you can start with searching for "DTS, object model" in the index.
That should get you started.
James C Loesch
Edited by - jloesch on 06/03/2002 7:16:27 PM
James C Loesch
June 6, 2002 at 9:18 am
James,
Your code works fine when I execute the DTS manually, but when I schedule the package, it appears to just sit and run without moving forward. This might be due to the fact that I have MsgBox popups to inform me of the progress within each of the DTS packages (for testing purposes). But, I never see the MsgBox popup at my workstation. Can you (or anyone) provide insight into this issue?
Thanks!!!
June 6, 2002 at 10:09 am
You will never see those message boxes, and the process will wait for a response to them, which will never come. Message boxes only work when you have a Graphical User Interface(GUI). SQL Server Agent doesn't provide one for use by scheduled jobs. It can't, because there's usually no user involved when the jobs run. You might try logging the information to a file on a network share instead. I have a generic procedure for that:
Sub LogInfo(strLogFilePath, strInfo)
Dim objFileSystem
Dim objLogStream ' Type is Scripting.TextStream
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objLogStream = objFileSystem.OpenTextFile(strLogFilePath, 8, True)
' 8 is for appending, True means it will create the file it it doesn't exist.
objLogStream.WriteLine strInfo
objLogStream.Close
Set objLogStream = Nothing
Set objFileSystem = Nothing
End Sub
This will append whatever string you pass in to the end of the file indicated by the file name parameter. This assumes that you pass in a valid file path. If not, you will get an error.
James C Loesch
Edited by - jloesch on 06/06/2002 10:16:56 AM
James C Loesch
June 6, 2002 at 11:04 am
Below is a quote from the thread: DTS works but schedule JOB don't...
Microsoft has posted this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074
It seems to clarify the issue of using a msgbox and never seeing the popup because the DTS package is running on the server.
June 6, 2002 at 11:25 am
Did you post the correct link? That article doesn't mention MessageBoxes or any GUI elements at all.
James C Loesch
James C Loesch
June 6, 2002 at 11:56 am
Right, but what it does say is this...
From the section of the article subtitled:
Where is the DTS Package Running?
...When you run the package from the DTS Designer in SQL Enterprise Manager (SEM), the package is running on the computer where you are seated...When the package is run as a scheduled job, the package is always run on the server.
This implies to me that when I run my DTS with the msgbox at my workstation, I will see the popup. But when run as a job, it runs on the server, so the server would see the msgbox and wait for user interaction which never happens because I'm at my workstation. Thus the package would just sit there and wait and not move forward to the next step in the DTS.
That's how I've interpreted this bit of information in this article. Would you agree with this assessment?
June 6, 2002 at 11:56 am
Right, but what it does say is this...
From the section of the article subtitled:
Where is the DTS Package Running?
...When you run the package from the DTS Designer in SQL Enterprise Manager (SEM), the package is running on the computer where you are seated...When the package is run as a scheduled job, the package is always run on the server.
This implies to me that when I run my DTS with the msgbox at my workstation, I will see the popup. But when run as a job, it runs on the server, so the server would see the msgbox and wait for user interaction which never happens because I'm at my workstation. Thus the package would just sit there and wait and not move forward to the next step in the DTS.
That's how I've interpreted this bit of information in this article. Would you agree with this assessment?
June 6, 2002 at 11:57 am
Oops -- sorry about the double-post
June 6, 2002 at 12:26 pm
Yes, the message box pops up (sort of) on the server, but if you went to the server and looked, you wouldn't see it. I guess you could say it goes to a behind the scenes interface that you can't connect to. I just tested it, and the computer beeped, but there was no dialog that I could see.
So, I guess I mostly agree.
James C Loesch
James C Loesch
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply