I major help...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, I've got 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),"/","")

Dim objFSO, strFullDir
Set objFSO = CreateObject("Scripting.FileSystemObject")
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")
set rs = DTSGlobalVariables("txtSQLDates").value
rs.MoveFirst

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

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

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

' 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") = "'" &
DTSGlobalVariables("InputDate") & "'"
oPkg.GlobalVariables("FileName") = DTSGlobalVariables("FileName")
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

Perhaps what you want is something like

1. Get you 3 dates when exporting on a monday into a recordset just like you are.
2. For each of those dates you can then do all that you need to do

a. Create a folder for each date
b. Set the SourceSQLStatement of the datapump task (SQL Server --> Excel) to filter by this date
c. Call the datapump in a loop.

similar to

Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?6,103,246,0,1


--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Thanks - I actually found that article shortly after posting and I ended up
doing something very much like what you have suggested. Everything seems to
work just fine - sending the file to Excel.

I have encountered a new problem, though, with exporting to Text files. I
use the Wizard to create a Transform Data task. I set the query to "Select
* from NightlyAccounting where orderdate = '...'" (... is of course a valid
date)...then the next tab is to specify the columns in the Destination.
When I attempt to Execute (to put the columns into the list), nothing
happens, and then I get an MMC error and booted out of Enterprise Manager.
If I do the same thing again, but this time only specify a few columns
(rather than *), it works...My table has 54 columns in it...

Is there a limit on columns in this method? It works just doing an Export
Data wizard... I'm baffled.

Thanks,
Andrew

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote