Calling stored procedure with parameter from tSetGlobalVar

[BartG]

Hi all,

I use TOS to create a random number SessionID into a variable in my tSetGlobalVar, read a log file and write the log file data to a table in a SQL Server database. This works.

After that I want to call a stored procedure on SQL Server with that SessionID in tSetGlobalVar as a parameter. This stored procedure will parse and transform the data in the table, and return the data in an easy-to-process form for TOS to write to separate files again.

The problem is I don't see how I can pass this SessionID variable as a parameter for calling the stored prcedure.

In this schema I created a SessionID field, and I think this is the way to do it, but this is of course just a schema definition. It's not tied to the real SessionID variable from tSetGlobalVar yet. How do I pass this value to the stored procedure and execute it?

Thanks!

[Anonymous · Accepted solution]

use a tFixedFlowInput to generate an input data flow that contains the SessionID data, define one column called 'SessionID' on the schema, and set its value as: (String)globalMap.get("keyName") in the 'Use Sing Table' model table.

...onsubjobok--tFixedFlowInput--main--tDataParseData...

Regards

Shong

[BartG]

Hi @Shicong Hong​ ,

Thanks for the advice to add tFixedFlowInput. I added this, but now I get an error about my recordset fields that come from the stored procedure not being found: "OutputLine cannot be resolved or is not a field". But I really can't see what is wrong.

I'll give you some screenshots to make things clear:

The error when I run:

This is what the stored procedure returns when I execute it in SSMS:

[Anonymous]

You don't set the value for each column to map the real field name on tParseRecordSet component, eg:

Column --> Value

OutputLine-->"OutputLine"

[BartG]

Thank you! That was less intuitive...I missed that one from the sentence "In the Attribute table area, fill in each Value column with the corresponding column name in the MS SQL Server table that holds the personal information." in the docs. It works now, thanks for your support!