December 14, 2005 at 9:55 am
Hi ,
I want to set value of a global variable in one Sql Query Task and access in another .
I have created a global variable named o_val of the type string.
In first SQl query task i have set the Output parameter as Rowset.
My query is returing as
Declare @var1 varchar(10)
set @var1 ='abc'
select @var1 as o_val
This task is executing fine.
In the second query task i have got o_val as input parameter.
declare @ var2 varchar(10)
set @ var2 =?
insert into table1
(
col1
)
values
(
@ var2
)
While executing this task i am getting error message.
Now when i go to DTS properties , parameter o_val in now chaged to type Dispatch.
Could you let me know where I am going wrong.
Can I check in between two tasks the value Global variable has taken.
Regards,
Vikram
December 14, 2005 at 2:51 pm
Intresting question, I don't work much with parameters in SQL tasks, perhaps I've run into some of the same issues. This is a bit different that the path you were taking but I tested it and it works.
The first SQL query task was similar to your execpt my Output Parameter Type was "Row Value" (This was Mapped to an Output Global Variable I created called o_val)
Here's where it get's tricky, I wasn't able to do much with that global variable in a SQL query task (other than use it in a WHERE clause) so I went to what I know works, using a Global Variable in a Transform Data Task ActiveX Script type. I created a bogus text file then used it as source for the Data Pump task. The only transformation is type ActiveX and looks something like this:
Function Main()
DTSDestination("col1")=DTSGolbalVariables("o_val")
Main = DTSTransformationStat_OK
End Function
You may need to ensure your bogus text file contains one row.
Hope this helps.
Cheers...
December 14, 2005 at 3:00 pm
>>In first SQl query task i have set the Output parameter as Rowset
This is the wrong setting. It should be "Row Value" if you want to assign individual Select statement columns to their corresponding global variables.
By choosing Rowset, you're getting the IDispatch interface for an Entire ADO Recordset, instead of the individual value, hence the error.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply