Koen and Phil (and anybody else who is able and willing to help),

I am trying and it is really showing my lack of knowledge, I have a book on order but it won't arrive for another week or two and this is the missing piece in my data import puzzle.

I'm only hours away from being able to commence testing and it is the importing of this file and using the file date variable that is stopping me from finishing.

I'll let you know what I have tried so far and if someone can point me to a resource that will highlight why I am wrong and what I really should be doing ...

So I created a variable called FileDate with a package scope and data type date time. I then went to the variable properties, set the "EvaluateAsExpression" property to "true", went into the expression editor, and place the following as the expression to use

Function GetCreationTime ( @[User::FileName] As String ) As DateTime end function

This failed as "Attempt to parse function failed". Ok so I know I'm getting something wrong.

I then thought I'd had an epiphany and that what I needed was a script task that got called wtihin the derived column task.

So within the data flow I thought I should add a script task to populate the variable, then used the derived column task to use the variable and pass it to the destination. But there was no script task, just the script component. I look at this and did not think it was what I was after so I halted there and still have no understanding of the script component.:ermm:

OK maybe the script task should be before the data flow within the foreach loop container. I decided that the script task needed to be before the data flow so that the variable value would be set and available to the data flow.

So within the Foreach Loop I added a script task as the first task in the flow, followed by the data flow and finaly the move to processed files system task. Now it is time to configure the script task.

So in the script task editor I selected VB2008 as the scripting language, set the read only variables to be the filename variable used within the foreach loop container and the ReadWrite to be the FileDate variable. I then clicked "Edit Script" to copy and paste the code linked by Koen. (Remember I am use to only using SSIS to pump data into a staging environment and then using stored procs to manipulate the date). So the code in the script task window now look like

"Public Shared Function GetCreationTime(ByVal path As String) As DateTime

End Function

"

I then went to the variable window, clicked on the FileDate variable and started setting its properties. I set the EvaluateAsExpression to "True" then clicked on the elipse against expression. I then entered GetCreationTime( @[User::FileName]) in the Expression window, clicked OK and got errored, "The function "GetCreationTime" was not recognized. Either the function name is incorrect or does not exist."

The scope for the FileName and FileDate variables are both set to "Package".

Thanks

Mark