December 30, 2005 at 10:51 am
Greetings -
Is it possible to reference a System Environment variable as part of the "File Name" for a Text File Source in a DTS script. I would like to allow the actual source directory to be dynamic and not hard coded. So far all my attempts have failed at referrencing an environment variable here.
Same would be true in referencing an environment variable when declaring the location of log files for Transform Data Task.
Thanks.
December 30, 2005 at 11:54 am
In DTS, you generally do stuff like this by having the package contain an initial ActiveX script task. This task modifies the properties of the other package tasks and connections. To set a filename from an environment variable, your ActiveX script task would look something like this:
Option Explicit
Function Main()
'Get a windows shell object
Dim oSH
Set oSH = CreateObject("WScript.Shell")
'Get an object representing the system environment
Dim oEnv
Set oEnv = oSH.Environment("SYSTEM")
'Get an object representing this package
Dim oMe
Set oMe = DTSGlobalVariables.Parent
'Dynamically set the properties of your Text File connection object
With oMe.Connections("YourConnectionNameGoesHere")
'Set the datasource to be the contents of an Environment variable
.DataSource = oEnv("YourEnvVariableNameGoesHere")
End With
'Cleanup
Set oEnv = Nothing
Set oSH = Nothing
Set oMe = Nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply