Using @USER0 as Default Value of Job Parameter

[yserrano]

I have a before job subroutine function which does an ODBC connection and then query a table to get a date based on some values given by the user. This date is then used along the entire job.

Inside the function I have this assignments:

Code: Select all

Errcode1 = SQLBindCol(hStmt, 1, SQL.B.DEFAULT, res1)
** ... (more code)
@USER0 = res1;
** ... (more code)

Then, in the Job Properties window, I have a job parameter with its Default Value set as #@USER0#

I checked the log and @USER is receiving the correct value in the function. But, in the job if I try to use the job parameter its value is "#@USER0#" as a string.

I am embarrassed here in the office because I announced yesterday that I have the function(ality) ready.
I swear I tested it and it worked ok.

I am probably being victim of a global conspiracy.

[Krazykoolrohit]

I dont think that is how you use a subroutine to feed your job. I wonder if that can possibly be done.

What you can do is to build a sequencer and use you routine (in execute command stage) to populate a date variable that can be passed onto the job in next step.

[chulett]

A couple of problems.

1) You can't use a parameter in a parameter.

2) You can't set the value of a parameter 'before job' as the job has already started. It's not as before as you may think.

Now, you may be able to simply reference @USER0 inside your job without the hashes, but no guarentees there, seeing how it is being set before job.

Why not create a job that gets the information and stashes it somewhere, say USERSTATUS for example. It can then be easily passed to other jobs as a 'normal' job parameter.

[yserrano]

@Krazykoolrohit: Thank you, we are considering your suggestion.

1) You can't use a parameter in a parameter.

You said this because of the syntax I am using in the default value: #@USER0# Right?

2) You can't set the value of a parameter 'before job' as the job has already started. It's not as before as you may think.

This means that the before job subroutine runs after the "default values" are assigned to the job parameters?

... seeing how it is being set before job.

Sorry, I corrected that and edited the main post. Thank you.

Why not create a job that gets the information and stashes it somewhere, say USERSTATUS for example. It can then be easily passed to other jobs as a 'normal' job parameter.

I am going to try yours and Krazykoolrohit suggestions.
If you have any sample code for the use of USERSTATUS I'll appreciate if you share it.

Thank you,

[chulett]

1) Yes.
2) Yes.

If you search for USERSTATUS you should find a number of discussions on how to use it and some code to make it easier as well.

[ray.wurlod]

You can use @USER0 in an expression provided it is in the same process, but it is a system variable, not a job parameter, and therefore does not take the "#" characters surrounding it.

You can, in a job sequence, use a system variable to provide a value to a job parameter in a Job activity.

[yserrano]

This is how we solve it:

- Changed the before job subroutine to a Transform routine.
- Created a Job Sequence with two stages: Routine_Activity and Job_Activity.
- From the routine activity stage we are calling the transform function.
- Used the Routine Activity Return Value as "value expression" for a parameter in the Job activity stage.

It is working fine. Thank you again to all