Export Multiple files via a loop

[m0nkey]

Hi All,
I have a package that selects some data, inserts it into a staging table, then exports the data to a file which has the filename appended with the current date and some global variables...I would like to have the query that pulls the data out of the staging table to loop and ultimately create multiple txt files appending each one with the loop criteria...I have simplified the query to show you the last pull for the txt file:

       Declare @start int
           Set @start= 1
            While @start < 5
              Begin
               Begin Transaction
              select * from import_test where chunk = @start
          Commit
        Set @start = (@start + 1)
       End

Each time it loops through i want to create a new file...

Here is the ActiveX that changes the file name:

Option Explicit

Function Main()
	Dim oConn, sFilename, voxno

	voxno = DTSGlobalVariables("voxno").value
	sFilename = "ex" & Right(Year(Now()), 2)  & _
	voxno
	If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
	    Month(Now()) Else sFilename = sFilename & Month(Now())
	If Day(Now()) < 10 Then sFilename = sFilename & _
	    "0" & Day(Now()) Else sFilename = sFilename & Day(Now())
	sFilename = DTSGlobalVariables("LogFilePath").Value & _
	    sFilename & "-" & _
	    voxno & ".txt"

	Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
	oConn.DataSource = sFilename
	Set oConn = Nothing

	Main = DTSTaskExecResult_Success
End Function

Now i have found plenty of code and use it quite a bit to import multiple files and loop through a directory but am stumped exporting files via a loop...

Any help would be appreciated...

Regards,
Mark