SSIS PACKAGE, Only want Derived Columns

Question

I have a SSIS Package that I have a For Each Loop which imports multiple txt files into a SQL Server table. That runs fine.

What I am trying to accomplish is to store the distinct filename and date it was imported into a separate table. I created a separate For Each Loop for this and then archive the txt file after it's complete with a File System Task.

The issue I am having is I put an event handler to invoke a SQL Task and Send Email task if there is a warning (I was hoping for a warning only if there were no files in the directory where the package is importing from).

However, I found a warning that a column in the Data Flow task was not being used and should be removed if not needed. But the Data Flow task requires at least one field for me to put a Derived Column task

Derived Column Field1: pulls the @User: CurrentFile from the ForEachLoop Container. Field2 pulls the current date.

Is there a way to perform this without the warning?

Answer 1

It sounds like you're over-complicating thing.

You have a ForEach loop and you're therefore assigning a value into some Variable to contain the file name, @User::CurrentFile. You can get the date it was loaded through either a call to GETDATE() or reference the system scoped variable, StartTime @[System::StarTime]

The most straight forward option would be to add an Execute SQL Task wired up to the OnSuccess Precedent Constraint from your Data Flow Task. The Execute SQL Task will then have a statement like INSERT INTO dbo.MyLog(FileName, InsertDate) SELECT ?, ?, assuming OLE DB Connection Manger, and then you map in your two variables.

Easy, clean, no warnings fired about unused columns in your data flow.

What I think you have is something like this, based on

I created a separate For Each Loop for this