Passing a variable to proc in DTS

Question

Passing a variable to proc in DTS

J Hall

Grasshopper

Points: 12
More actions
October 6, 2005 at 4:27 pm

#67575

In the package I am creating, I have an Execute SQL task that creates a table with a field called HeaderID. Later in the process in a separate SQL task I am trying to access that HeaderID field (single row/single field) as a procedure's variable. The variable will be dynamic since it's value is not known until midway though the package's execution.
select HeaderId from DTImport <--value
Declare @log_id int <--datatype
exec RecordDebt @log_id <--the proc
I have tried doing it as a global variable but could not get it to work. I have done searches on Google and several boards. I am not very good with VB so I am having trouble understanding the ActiveX examples I have seen. If someone could point me in the right direction or if they have a template of some sort I would appreciate it.
Thank you in advance.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mike gercevich SSCrazy Points: 2961 More actions · Answer 1

I'm not quite sure of what you are asking here... If you are asking how to pass a global variable to a stored proc that excepts a single parameter, @HeaderID, then you would format your EXEC SProc statement like so:

EXEC dbo.RecordDebt @HeaderID=?

-- OR --

EXEC dbo.RecordDebt ?

In you Input Parameters tab, you would place your global variable in to replace the '?'. So if you had a GV named gvTest that was 724. The Statement would be executed as...

EXEC dbo.RecordDebt @HeaderID=724

The Result of your SProc can be returned into a separate GV, defined in your output parameters tab.

If this is not what you are asking... Please restate your question.

-Mike Gercevich

ND-215452 SSCrazy Points: 2037 More actions · Answer 2

You probably don't need activeX.

how about something like this....

1. create a global variable called HeaderID

2. create a query (stored proc) to output HeaderID and assign it to the global variable (using sql task).

eg. select headerid from table1 where......

3. from sql task, exec RecordDebt ? -- where ? is your global variable

J Hall Grasshopper Points: 12 More actions · Answer 3

Ok....I understand what I was doing wrong, I was trying to put the input and output parameters inside the same task. Now I understand how it works together. Thanks for showing me the error of my ways. I appreciate it.