I have 5 folders each having 2-3 text files.
I have two DTS(SQL 2000) packages one which is having around 12 tasks which
is getting processed based on the records in '.txt' file, now what i need to
do is put this entire first package in a loop which will get the name of the
file from my second package which is saving the file name in a global
variable.



This is the activeXScript of my second package which is passing the name of
the text file to a global variable -

For Each objsubfold in foldSubfolders
for each fil in objsubfold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.Name
Next
Next


help needed asap



Nitin

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function

Nitin wrote:

Here's how I do it. (This is on SQL Server 2000). I create a function named ExecuteDTS in the outer package that
executes the inner package. The critical piece in making this work is creating the DTS.Package2 object in the function.
I equate the values from the DTS.GlobalVariables in the outer package to those in the function. In the sample below,
I select mail addresses from a view and loop through the record set in the outer package and call an inner package that
sends the mail. In this case, both packages reside on the server.



HTH

Ed

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")

strConn = "Provider=MSDASQL;Driver={SQLServer};Server=myserv er;Database=mydb;UID=myusername;PWD=mypassword"

oCnn.Open strConn

If oCnn.State = 1 Then
strSQl = "select distinct Email from mytable"


rs.Open strSQl, oCnn

If Not rs.EOF Then
rs.MoveFirst

While Not rs.EOF

DTSGlobalVariables("email").Value=rs("Email")
DTSGlobalVariables("subject").Value = "Items awaiting approval"

call ExecuteDTS("myserver","myusername", "mypassword","My DTS package name")

rs.MoveNext
Wend

End If
End If

Main = DTSTaskExecResult_Success
End Function


Function ExecuteDTS(servername,username,password,PackageNam e) 'Run the package

Dim objPackage

Set objPackage=createobject("DTS.Package2")

objPackage.LoadFromSQLServer servername,username,password,CPPDEFAULT,CPPDEFAULT ,CPPDEFAULT,CPPDEFAULT,PackageName

objPackage.GlobalVariables("email").Value = DTSGlobalVariables("email").Value
objPackage.GlobalVariables("subject").Value = DTSGlobalVariables("subject").Value

'Run the package and release references.

objPackage.execute

End Function