session variable ? urgent

[vikind]

I have a pre-session stored procedure that returns a value i want to assign the return value of stored procedure to
a mapping or session variable such that whenever any of the mappings run they can use that session varaible
with its value and populate it into the target table. would appreciate if anybody can help me with this.

as i have to use the return value generated by the stored procedure in all my mappings

thanks for ur help

 

[ArtieChoke]

Variables come in two types - one is stored in the repository and updated by the map - the other is stored in a parameter file. I don't know any way to set a session variable outside of a parameter file (and I've looked at doing it from the SDK, etc.). The only way I can see that you can do it is for your SP to store that value in a file.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[vikind]

hi artiechoke,

in that case how can i assign the stored procedure return value to a parameter in the file. can i just create a new parameter but how will the parameter get the value from stored procedure..?. as return value from stored procedure

 

[vikind]

the parameter file it shud be something like

Folder.Session_name

ret_val = (value returned by stored procedure)

and my mapping should read the ret_val from the parameter file.

 

[ArtieChoke]

Will you be using the same value from the SP for all the sessions?

Are you aware for each session that uses the same parameter, there must be an entry in the parameter file with the session name, even if you're using the same parameter name.

One thing I just became aware of is a SP transform that allows you to get a return value from an SP and use that in the mapping. Depending on what you're doing, this might work out.

Otherwise, the only way I can see to have your SP put out a value to a file is something like this using the xp_cmdshell procedure to run a batch command:

DECLARE @cmd varchar(20), @var varchar(20)
SET @var = 'dir /p'
SET @cmd = 'echo ' + @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

You could set up a batch file that does the work and pass it the value you want to use.



"I think we're all Bozos on this bus!" - Firesign Theatre