Integration Services :: Get FileName Fo Each File Created Via Dynamic Flat File Destination

Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?

Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’

E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt  etc} using Foreach Loop Container  :

* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created

*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4

For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:

@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"

All this successfully creates these 4 files:

Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt

Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:

CREATE TABLE dbo.MMMAudit
  (
     AuditID      INT IDENTITY(1, 1) NOT NULL,
     PackageName     VARCHAR(100) NULL,
  
FileName           VARCHAR(100) NULL,
     LoadTime        DATETIME NULL,
     NumberofRecords INT NULL
  ) 

To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:

Execute SQL Task

Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below

SQLStatement: INSERT INTO [dbo].[MMMAudit] ( 
PackageName,NumerofRecords,LoadTime)
 (?,?.GETDATE)

Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?

AuditID PackageName FileName  NumberOfRecords
1           MMM       NULL                      12
2          MMM  NULL                23
3          MMM  NULL      14
4          MMM  NULL              1