Hello.
SQL Server 2000
I would like to change a global variable in runtime.
I have a package which have Execution Task as
SELECT orderID
FROM orders
WHERE orderID = ?
I also defined a GLOBAL VARIABLE called my_var =10249 as input as well as
output as row value .
Now I would like to change it by calling stored procedure or DTSRUN utility.
Here is a part of my SP

EXEC @hr = sp_OAMethod @object,
'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
@PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd

exec @hr = sp_OAsetproperty
@object,
'GlobalVariables (''my_var").Value', 10255 -----set
global variable
EXEC @hr = sp_OAMethod @object, 'Execute'

EXEC @hr = sp_OADestroy @object

--------------
exec master..xp_cmdshell 'dtsrun /Sserver_name /Uuser_nName /Ppassword
/Npackage_name /Mpackage_password /Amy_var :typeid=10255'

Both methods don't change the variable

Can someone shed light on the problem?

You will not see the value changed when you reopen the package. It will be
your design time value.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Ok So here is my test

CREATE TABLE TableA(col1 int)
GO
INSERT TableA VALUES(1)
INSERT TableA VALUES(2)
INSERT TableA VALUES(2)
INSERT TableA VALUES(1)
INSERT TableA VALUES(3)
INSERT TableA VALUES(3)
INSERT TableA VALUES(4)


My Query that i use in the DataPump is

SELECT * FROM TableA WHERE col1 = ?

Global Variable is named "var1"
Package Name is MyPackage


I'm going to reuse your code.

declare @packagename varchar(255) --package name, gets most recent version
declare @userpwd varchar(255) --login pwd
declare @intsecurity bit --use non-zero to indicate integrated security
declare @pkgPwd varchar(255) --package password
declare @hr int
declare @object int

set @packagename='MyPackage'
set @userpwd=null
set @pkgPwd=''
set @intsecurity=0

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT

declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = @@servername
declare @flag int
select @flag = 256



EXEC @hr = sp_OAMethod @object,'LoadFromSqlServer',NULL,
@ServerName=@svr, @ServerUserName=@login,@PackageName=@packagename,
@Flags=@flag, @PackagePassword = @pkgPwd

exec @hr = sp_OAsetproperty
@object,
'GlobalVariables ("var1").Value', 4 -----set global
variable

EXEC @hr = sp_OAMethod @object, 'Execute'
EXEC @hr = sp_OADestroy @object

Works fine for me.

Make sure the Global Variable is actually mapped to your parameter and makes
sure in your code above the case is right for the GV name as well.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Thank you Allan.
But when I execute my SP and give the new values to the global variable , so
the package does insert the old value
Here is a repro
Inside the Execute Task I wrote below query and define the variable =10249
as input only.
---------------------------
insert into newemployees SELECT orderID
FROM orders
WHERE orderID = ?

--------------------------
CREATE proc uuu
as

declare @packagename varchar(255) --package name, gets most recent version
declare @userpwd varchar(255) --login pwd
declare @intsecurity bit --use non-zero to indicate integrated security
declare @pkgPwd varchar(255) --package password
declare @hr int
declare @object int

set @packagename='package'
set @userpwd=null
set @pkgPwd=''
set @intsecurity=0

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT

declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = @@servername
declare @flag int
select @flag = 256



EXEC @hr = sp_OAMethod @object,
'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
@PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd

exec @hr = sp_OAsetproperty
@object,
'GlobalVariables ("var").Value', 10255 -----set global
variable

EXEC @hr = sp_OAMethod @object, 'Execute'
EXEC @hr = sp_OADestroy @object



Thank you

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Allan, Thank you for your patience.

Did you run the SP in QA? Where have you seen the output after running the
SP?
What value of GV did you put inside (when you create) Execute SQL Task?

Something wrong ?

Again I have within my package MicrosoftOLEDB connection to the NorthWind
database and Execute SQL Task
which contains SELECT * FROM TableA WHERE col1 = ?
I puted during creation GV inside Execute SQL Task the value var1=4 Am i
right? and then wrapped the code in SP and run it in QA.



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I ran it in QA yes
The output from sp_OA* is limited but I got a "The command(s) completed
successfully."
As I said I had a table as detailed and I had the SELECT statement with the
parameter ?. I mapped the paramater to my Global Variable.
I changed the value of the Global Variable through the code as per your
example.

You are changing the SQLStatement of the ExecuteSQL task through your
example if I read it correctly. What do you intend to do with the results?
This Query will return a rowset to you.

Try this


Create a DTS package
Inside is 1 * DataPump (SQL Server to Text File)
Create your Glbal Variable
Output to a text file on your desktop
Use the Query I posted as the Source query
Map the ? to the Global Variable
Now save the package
Fire the package as per your code.

Works for me.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Allan ,thanks
I have been trying to insert into another table the data based on GV
parameters.
As I said it does not happen.
GV
It does insert the data which based on the old value.
?. I mapped the paramater to my Global Variable.
I also mapped it let me say to 4 in youR example but when I run my SP I was
expected to see 2 like I change it in my SP
( I cannot see a new value in a package as you are mentioned) but at least
why does not give me to insert a new one after running my SP?
INSERT INTO NewTable SELECT * FROM TableA WHERE col1 = ? (mapped to 4 and
changed to 2) despite this id inserts a value 4



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

From your description it looks as though you are doing things correctly.
What datatype is your GV? Mine was "integer".

I can send you my package if you wish.

What SP are you using?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Allan
Thank you
Yes, I wish that you send me a package. The datatype is also "integer".
Does it matter I am sitting on MSDE (SQL Server 2000) ?




"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote