| Author |
Topic |
|
barretld
Starting Member
22 Posts |
 Posted - 2009-01-16 : 10:18:09
|
| Hey all, i have a ssis package with a for each loop to grap all files then import into database, using a system derived column of @[System::StartTime] to put in a field called created_date. Works good, however problem is I do not want the system starttime, but rather the date modified of the actual file loaded into the database that is grabed by teh for each loop. Basically, my question is how do i put in the date modified of the text file of each file, into a variable? End result would be a table in sql server that shows the date modified of the actual file loaded for that field created_date. Thanks. |
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2009-01-16 : 10:45:55
|
| File.GetLastWriteTime("C:\Test.txt").ToString() |
 |
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-16 : 11:03:30
|
| Thanks.....any ideas how i put this in the package's derived column icon thing? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:10:59
|
quote:
Originally posted by barretld
Thanks.....any ideas how i put this in the package's derived column icon thing?
put this in script task and populate this value to variable that you create in package. Then use the @[User::YourVariable] to populate the table |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-16 : 11:18:56
|
| sooo, thinking about it....let's see......have: foreach loop container,flat file source, derived column, ole db destinationu suggest: script component, then what? |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-16 : 11:27:31
|
| i think i might have it, thanks...... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:31:47
|
| welcomelet us know how you got on |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-16 : 11:35:39
|
| ok, ill be working on it ,never used it before.....wondering how to set it to variable....' datemodified script by barret danielImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' File.GetLastWriteTime("C:\Test.txt").ToString() ' Dts.TaskResult = Dts.Results.Success End SubEnd Class |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:38:43
|
| useDts.Variables("YourVariableNameHere").Value=File.GetLastWriteTime("C:\Test.txt").ToString() |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-16 : 11:45:43
|
| the thing is it loops through the file each time, so the file changes from c:\Test.txt to c:\Test2.txt and so on until all files are run through....so i'm thinking something other than .getlastwritetime("C\Test.txt").ToString() is needed? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 12:05:28
|
quote:
Originally posted by barretld
the thing is it loops through the file each time, so the file changes from c:\Test.txt to c:\Test2.txt and so on until all files are run through....so i'm thinking something other than .getlastwritetime("C\Test.txt").ToString() is needed?
then use for each file enumerator and put script task inside it. The for loop will loop through all files in specified path (in your case C\:) and then you can do reqd processing inside which will be repeated for each file.See this alsohttp://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-16 : 12:10:14
|
| thanks, that's what i was looking for.....i'll let ya know how it goes |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 12:15:04
|
ok...you're welcome  |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-01-20 : 13:18:30
|
| works well.....tthis is the code i used: Had to learn some of the basics of Microsoft Visual Basic .NET Dim path As String = Dts.Variables("DateModifiedPath").Value.ToString() Dim dt As DateTime = File.GetLastWriteTime(path) Dts.Variables("DateModified").Value = Cstr(dt) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 13:20:14
|
| cool |
|
|
|
|
SSIS Variables....