Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

How to Execute Linked Stored Procedures in a DTS

I am a little unfamiliar with designing DTS packages, but what I want to do is visually display 5 stored procedures linked together and save that as a “Job” or “Package”. I then want to be able to call the “Job/Package” from a stored procedure.

I know I can use DTSRUN to call the package. I tried this with a very simple package that consisted of a connection and a select statement like this:

SELECT ‘this is a test’

The output I got when I ran the package from Query Analyzer was:

output                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1
DTSRun:  Package execution complete.
NULL

(6 row(s) affected)



Why didn’t it print ‘this is a test’?

Also how do I pass input prameters to the package and get output prameter back from it via Query Analyzer?
0
paulfryer
Asked:
paulfryer
  • 9
  • 5
1 Solution
 
Anthony PerkinsCommented:
>>Why didn’t it print ‘this is a test’?<<
Because the package ran on the server.

>>Also how do I pass input prameters to the package <<
Declare global variables and pass them using /A if you are using DTSRun.  

>>and get output prameter back from it via Query Analyzer<<
You can Insert it into a table.  Or use ActiveX and use a MessageBox.  Of course, this last cannot be used when the package is run unattended.
0
 
BillAn1Commented:
outputs (i.e. results from select statements) from stored procedures etc do not cascade.
If you call procedure A which in turn calls procedure B, you will only see the outputs from procedure A, the outputs from procedure B will be lost.(unless procB does a print of somethign, which will still be seen, if you happen to be useing something like QA)
The results of select statements are returned not to the output screen, but to the calling process, eg in this case procedure B returns it's outputs to procedure A. PRocedure A does nothing with these results, so they are 'lost'. It is only when you call procedure B directly from an application such as QA, where the application catches the returned record set, and displays it on the screen that you actually see the results.

There is no simple way to concatenate the results of multiple stored procs together, this is the job of your application layer, to call each procedure in turn, and then catch the results of each.

Note, the main purpose of DTS is, as the name suggests, Data Transformation. It is intended to DO something, rathter than for returning record sets. One feature of DTS packages is that you can execute SQL commands, but the reson you would do this is to have the procedure do somehting, like add new data / manipulate data in some way, rather than to return a result set.
If all you want to do is return record sets, then you should not be thinking about using DTS packages.
0
 
paulfryerAuthor Commented:
acperkins -

The task object I have when I design the DTS Package is a “Execute SQL Task”.  When I go to the properties of it, I can enter a SQL statement. There is a button called “Parameters”, when I click I can add Global Variables. What I don’t understand is how I reference the Global Variables in the SQL of the Task. I guess I still don’t understand how to map the input variables/global variables. Could you please explain that part in more detail?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
paulfryerAuthor Commented:
BillAn1 -

The reason I am considering using DTS packages is because I need a way to visually see what is going on. Some of my processes require 10 stored procedures to run at once. It gets very confusing to try to open 10 individual windows of code and go back and forth to see what input variable must match what output variable. What I need is a way to visually see/design the process with stored procedures represented as visual objects that can take information in and spit information out. I have not found a program that can do this with TSQL stored procedures so DTS is the closes thing I can find. I am open to sujestions for other visual TSQL development programs however.
0
 
paulfryerAuthor Commented:
Apparently I can map global variables to input parameters. How do I specify an input parameter?
0
 
Anthony PerkinsCommented:
>>Apparently I can map global variables to input parameters.<<
And assign output parameters to global variables.

>>How do I specify an input parameter?<<
With a "?", as in:
Select * from Customers where Country = ?
0
 
paulfryerAuthor Commented:
OK lets consider the folowing SQL:

DECLARE
      @UserName NVARCHAR(100)

SELECT     *
FROM         USERS
WHERE     (NAME = @UserName)

Now I think @UserName is the input parameter. When I click on the "Parameters.." button in the Execute SQL Task Properties box, I can't select anything where it says "Parameter Mapping". Its like the porgram dosn't recognize @UserName as an input parameter. I still don't know how to "MAP" the parameters.
0
 
paulfryerAuthor Commented:
OK, I see - I didn't know you where being literal. Use a "?". Thanks, I'll see if I can get somewhere with that.
0
 
paulfryerAuthor Commented:
What is the difference in the Output Parameter Types: (None, Row Value, and Rowset)?
0
 
paulfryerAuthor Commented:
Also how do you get output parameters out of the package? I mean how do I have the package do something, then get the values out of it so they can be referenced from my code locally.
0
 
Anthony PerkinsCommented:
>>Also how do you get output parameters out of the package?<<
For example, you want to get FirstName and LastName given UserName than your SQL statement would look like this:

Select FirstName, LastName Where UserName = ?

1. Click on Output Parameters.
2. Select RowValue
3. Assign Global variables.

0
 
paulfryerAuthor Commented:
buy how do you actually extract the output variable in the DTRUN sql statement?
0
 
Anthony PerkinsCommented:
>>buy how do you actually extract the output variable in the DTRUN sql statement?<<
Not following you here.  DTSRun allows you to pas in global variables on the command line.  Is that what you are referring to?
0
 
paulfryerAuthor Commented:
yes sort of. I am referencing DTSRun from a stored procedure. How do I get the output parameters from the DTS package back into my stored procedure?
0
 
Anthony PerkinsCommented:
Simply put:  You can't.  The best you can do is place the values in a table that you can reference from the stored procedure.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now