dogramone (2/11/2014)
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.
It's all good. The explanation of what you've tried and explored below definately shows this.
So I created a variable called FileDate with a package scope and data type date time.
So far, so good.
I then went to the variable properties, set the "EvaluateAsExpression" property to "true", went into the expression editor....
Definately not this. As you concluded below, you want to do this via a script task.
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.
You're overcomplicating it this way.
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.
On target.
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.
What setting did you use in the for each task here for the Retrieve File Name? What you'll want is Fully qualified to get the full path. The variable accessibility is correct, I just want to make sure you've got the full thing.
I then clicked "Edit Script" to copy and paste the code linked by Koen.
That's not what you wanted to do. If you look further down the page, you'll see examples of usage, like so:
Imports System.IO
Module Module1
Sub Main()
Dim fileCreatedDate As DateTime = File.GetCreationTime("C:\Example\MyTest.txt")
Console.WriteLine("file created: " + fileCreatedDate)
End Sub
End Module
So, here's basically the code you're looking for:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim filepath As String
filepath = Dts.Variables("ForEachLoopVar_FilePathAndName").Value.ToString
Dts.Variables("FileCreationDate").Value = File.GetCreationTime(filepath)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
I copied everything so you could see it, but you really only want to change it to include the Imports System.IO at the top and then the Sub MAIN() function.
Now, you use a derived column and add the variable in the data flow there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA