Creating 'public' variable inside job

[dennisroser]

Hi

i will try to explain what i am after

In our environment the writting into staging area tables, are to be partitioned (not Information server partitions)

Meaning. i have table named 'StagingData' that i want to load a new days data into.
A stored procedure has been created by the DBA that is to be called with my tablename as parameter, and it will return the name of the partition i am to use.
This partitionname will be my actual tablename in the job to use for the ODBC Connector that writes to the target.
When finished the job calls another stored procedure that swithches the partition on to the 'real' table.

All our jobs are being started by a JobSequence, in order to handle Errors causing abort and a lot of system stuff.

To be able to pass the above mentioned partitionname to use for the current run of the job, i had to add 2 stages to the sequence, first a job that calls the stored procedure, and writes the result to a file, then a 'Execute Command' stage that reads the file and use that in a parameter to the actual load job.

This works fine, but i think it is a clumpsy solution that, if more requirements of the same where i have to add 2 more stages every time, will make the sequence unmanageable.

I have been trying to find a way to instead have a dummy parameter to the load job and the updating the content of this in a pre run functionality but i can not see how i can do that

i have also tried with a DSRoutine that makes the call to the stored procedure and returns the partitionname. this routine i will then add to the 'Tablename' column in the odbc connector, but that it not possible either.

Hope my explanation of the problem was understandable, otherwise i will explain further.

regards

Peter

[chulett]

Your current way is the correct way and the only thing 'clumsy' about it is using a file for intermediate storage. Make your first job a Server job and put the output of the proc into 'user status' which is one of the public variables you have access to. Then you can link directly from the first job to the second and specify 'Job1_Stage_Name.$UserStatus' as the value to be passed to the appropriate parameter in the second job.

Search the forums here for DSSetUserStatus for the small 'interlude' routine you'll need to create for this.

[dennisroser]

thanks craig

the dssetuserstatus suggestion is working beautifully

regards

Peter

[chulett]