Changing Global Variables from a Stored Procedure

[GJP55]

How do I set certain Global Variables for a DTS from a stored procedure ?

I have a DTS set up with an active X script that defines the File Location for an excel file import

The File location is set as a global variable in the DTS but I need to be able to change the file location from a stored procedure and the execute the DTS.

Everything is set up to do this except changing the global variable.

Thanks

 

[JohnDTampaBay]

This article describes how to instantiate a package, update its global variables, and execute the package - all from T-SQL. I hope it helps. Good luck!

http://www.databasejournal.com/features/mssql/article.php/1459181

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[GJP55]

Exactly what I was looking for.
Thanks very much

 

[GJP55]

I have used the code found at the website link and ran it but it does not seem to change the global Variable called FileLocation.

-------------------Create Object----------------------------

Declare @hr int, @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT

-------------------Set the Package--------------------------

EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("Server1", "", "",56, , , , "ExcelFileName")',
NULL

------------------Set a Global Variable---------------------

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("FileLocation").Value', 'C:\Test.xls'


Anybody any idea's ?

 

[JohnDTampaBay]

If you run this code then open the package to check the value of the variable, you won't find anything changed. The value in the actual saved package does not change, only the value in this instance of the package (the one created using sp_OACreate) changes.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)