Execute Package with Input Parameter

[Andrew888]

I need major help, please...I've got a table that has invoice information. As a Nightly
process (only on weekdays) - we need to export the data to an Excel or text
file. On Monday nights, however, we may have invoices that generated on
Saturday and Sunday - so when the process runs on Monday night, we need to
separate the data by invoice date. The whole package does a lot more than
this, but that is the part I am stuck on.

I have in the package an "Execute SQL Task" that runs the
following query and outputs the rowset to a global variable "txtSQLDates":

select distinct convert(varchar(10),orderdate,101) as 'date'
from NightlyAccounting

After this, I have an ActiveX task with the code below. This code first
sets up a folder on the server with the date as the folder name - it is a
requirement of our design (to separate the files in a folder by run date).
Next, I put the "txtSQLDates" global variable rowset data into a RecordSet.
Then I iterate through the RecordSet, attempting to set 2 input parameters
of another package. The second package is a simple SQL Connection, an Excel
Connection, a Dynamic Properties Task (which uses the global var. date to
set the Excel filename), and a Transform Data Task (which uses the global
var. date to set an input parameter to pull rows that only have that invoice
date). I get through everything okay - the 2nd package launches, but no
file is ever created. I set up MsgBox's in the 2nd package to tell me where
I'm going wrong, and it sets the Dynamic location, but it never executes or
fails, or something on the Transform Data Task...does anyone have an idea on
where I am going wrong? The code is shown below...

Thanks,
Andrew

Function Main()

'Set up the folder
Dim myServer, myFolder
myServer = \\server myFolder = Replace(FormatDateTime(Date(), vbShortDate),"/","&quot

Dim objFSO, strFullDir
Set objFSO = CreateObject("Scripting.FileSystemObject&quot
strFullDir = myServer & myFolder

If (objFSO.FolderExists(strFullDir)) Then
strFullDir = strFullDir
Else
objFSO.CreateFolder(strFullDir)
End If

Set objFSO = Nothing

'Save the ResultSet GlobalVariable to a RecordSet
Dim rs
set rs = CreateObject("ADODB.Recordset&quot
set rs = DTSGlobalVariables("txtSQLDates&quot.value
rs.MoveFirst

'Set up the file
Dim myDate, myExtension
myDate = rs.fields("date&quot.value
myExtension = ".xls"

DTSGlobalVariables("FileName&quot = strFullDir & "\" &
Replace(myDate,"/","&quot & myExtension
DTSGlobalVariables("InputDate&quot = myDate

'Call child package
Dim oPkg, oStep
Dim sServer, sUID, sPWD, iSecurity , sPkgPWD, sPkgName, sErrMsg
Set oPkg = CreateObject("DTS.Package&quot

' Assign parameters
sServer = "server"
sUID = ""
sPWD = ""
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""
sPkgName = "DTSataFiles"

' Load & Set & Execute Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "",
"", sPkgName
oPkg.GlobalVariables("InputDate&quot = "'" &
DTSGlobalVariables("InputDate&quot & "'"
oPkg.GlobalVariables("FileName&quot = DTSGlobalVariables("FileName&quot
oPkg.Execute

For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Main = DTSTaskExecResult_Failure
End If
Next

oPkg.Uninitialize
Set oStep = Nothing
Set oPkg = Nothing

rs.MoveNext
Next



Main = DTSTaskExecResult_Success
End Function