Global Variable in DTS Package

[Guest_imported]

Hi,

Is it possible to insert value of a global variable into a Execute SQL Task in DTS Package Design? If so, how?

Thanks alot in advance.

 

[timscronin]

I am not a scripting wizard but the scriptbelow creates a global variable (filename) that we use to create a unique file for export on a daily basis.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim oConn, sFilename, ffilename
' Filename format - exyymmdd.log
sFilename = "\\Dms4\phar\"
ffilename = Year(Now())
If Month(Now()) < 10 Then fFilename = fFilename & &quot;0&quot; & _
Month(Now()) Else fFilename = fFilename & Month(Now())
If Day(Now()) < 10 Then fFilename = fFilename & _
&quot;0&quot; & Day(Now()) Else fFilename = fFilename & Day(Now())
fFilename = fFilename & hour(Now()) & minute(now())
fFilename = DTSGlobalVariables(&quot;LogFilePath&quot.Value & _
fFilename & &quot;.log&quot;
sFilename = sfilename & ffilename
Set oConn = DTSGlobalVariables.Parent.Connections(&quot;Text File (Destination)&quot
oConn.DataSource = sFilename
' oConn.DataTarget = sFilename
Set oConn = Nothing
DTSGlobalVariables( &quot;fname&quot.Value = ffilename
Main = DTSTaskExecResult_Success
End Function

 

[zhonghandle]

thank you for your reply. But what I intended to ask is that how to insert the value of a global variable into a &quot;Execute SQL Task&quot; of a DTS package.

Thanks alot.

 

[tlbroadbent]

There is a sample on Darren Green's DTS pages at SWYNK.com.

http://www.swynk.com/friends/green/dtsgvreturn.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.