This is a little lengthy but hopefully explains what I want to accomplish.
What I want to do:
I want to be able to call a sybase stored procedure and pass the result set to a file.
The stored procedure will have at least one parameter and the values for the parameters will be provided in a file. The file will have 100's of different values.
Basically I want to loop through these values one by one (many
procedure calls) and produce a file for each one. The filename will be the value in the file with .txt attached.
What I tried to do:
I created a new job server and placed an ODBC task and a
sequential file task onto the diagram editor.
I linked the two together from the ODBC task to the file.
On the Stage tab in ODBC I entered the relevant data source name information. (I initially tried using SYBASE_OC but its handling of stored procedures either is non-existant or not as easy to use as the ODBC task).
On the Outputs stage I selected the Stored Procedure option and imported the procedure that I wanted to run. In the Columns tab I specified the columns that match the result set coming back from the stored procedure.
In the Parameters tab I specfied the required details and supplied Values to be passed to the procedure.
On the Inputs tab on the sequential file I entered the required filename and on the columns tab entered the relevent details.
I then ran this and the data was written to the file as expected.
HOWEVER, I have a number of points regarding this.
(1) I want to be able to pass the parameters (from a file) to the procedure instead of having to hard-code them into the ODBC Output Tab, Parameter Tab. How can I do this ?
(2) The filename that is produced at the end of run time is one that I again hard-coded. I want this to be composed of the parameter value from the file + .txt. Again, is there a way that I can this ?
(3) I want this whole process to be written so that all values in the file are looped through one by one until they have all been processed. Again is this possible.
I've probably approached this in the wrong way so any advice would be welcome. I'm not sure if this can be done purely within a job server or if job sequences have to be used. This is a simple model that I need to set up before making things more complicated.
As always any help is appreciated.
Stored Procedures and Parameters
Moderators: chulett, rschirm, roy
Hi,
All 3 are possible.
regarding your parameters being a table/file you have 2 choices (at least as I see it)
1. foreach row you read via server job run a DRunJob in a transformer having the columns read pass thru to the DSRunJob Routine.
2. use some basic controll job to read the file/table and do hte same in a loop.
once you pass the relevant variables as parameters your home free
bare in mind that DSRunJob might need some work to hide encripted parameters such a passwords (it's been a while since I checked this it was the case in 5.2 I believe).
IHTH,
All 3 are possible.
regarding your parameters being a table/file you have 2 choices (at least as I see it)
1. foreach row you read via server job run a DRunJob in a transformer having the columns read pass thru to the DSRunJob Routine.
2. use some basic controll job to read the file/table and do hte same in a loop.
once you pass the relevant variables as parameters your home free
bare in mind that DSRunJob might need some work to hide encripted parameters such a passwords (it's been a while since I checked this it was the case in 5.2 I believe).
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Thanks for the help Roy. I've got this working to a certain extent but do not think I have done this the best way.
I have created 3 server jobs:
(1) Attaches to MSSQL and returns a number of rows to a file.
(2) Attaches to Sybase and runs a parameterised query writing the result set to a file.
(3) A job server that onlt contains Job Control Language.
It runs the job server described in (1) above.
It then opens the file produced in (1) and loops through it line by line. Each value is then used as a parameter to call the Job Server in (2) so no manual entry is required.
This all works fine but as I said possibly not done the best way. It means that the controlling job has no icons on the diagram editor.
I have a feeling that creating transforms or routines maybe beneficial here but don't have much of an idea how to start them off. Still working on management for personalised training !!!
Again, any pointers or help are most appreciated.
I have created 3 server jobs:
(1) Attaches to MSSQL and returns a number of rows to a file.
(2) Attaches to Sybase and runs a parameterised query writing the result set to a file.
(3) A job server that onlt contains Job Control Language.
It runs the job server described in (1) above.
It then opens the file produced in (1) and loops through it line by line. Each value is then used as a parameter to call the Job Server in (2) so no manual entry is required.
This all works fine but as I said possibly not done the best way. It means that the controlling job has no icons on the diagram editor.
I have a feeling that creating transforms or routines maybe beneficial here but don't have much of an idea how to start them off. Still working on management for personalised training !!!
Again, any pointers or help are most appreciated.
Nothing wrong with that at all. If you are worried about it, drop an annotation on the canvas explaining what the job is doing.palmeal wrote:This all works fine but as I said possibly not done the best way. It means that the controlling job has no icons on the diagram editor.
If you have version 7.5 you may be able to accomplish this via the GUI in a Sequencer job, thanks to the introduction of the Start Loop and End Loop stages.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers