Environmental Variables through DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Environmental Variables through DTS

Hi, I have created an Environmental system variable by name MyEnvironVar and am trying to access this in DTS using the Active X script Task property. Following is the code snippet Set wshell = CreateObject("wscript.shell")
if Isobject(wshell) then
v_xmlfilepath = wshell.ExpandEnvironmentStrings("%MyEnvironVar%")
msgbox v_xmlfilepath
end if
Set wshell = Nothing The output is "%MyEnvironVar%" which is not I want. I need the value that I have set. There was a discussion on this forum sometime ago and I can access this environmental variable value through a stored proc using xp_cmdshell. But I need to do this now in the DTS. Any help is highly appreciated.. Thanks in advance
In VBA (VB version for Office) you can call the Environ("MyEnvironVar") function – is there no equivalent in VB Script? The trick you mention was in T-SQL, using xp_cmdshell to issue the command SET MyEnvironVar which returns the string MyEnvironVar=whatever, including any spaces around the = sign, exactly as entered in the definition of the environment value.