Script Task - How to get File.GetCreationTime

[Database Administrator]

Hello,

I'm trying to use SSIS to import a flat file into my db. However, I want to
also insert the date the flat file was created. I want to use the script
task, but I'm new to VB.Net.

Can someone please tell me how to do this? I've already spent over 12 hours
trying to get it work but to no avail.

I believe I should use something like:
File.GetCreationTime("C:\flatfile.txt"), but I don't know how to set up the
code and assign the create date to a variable so I can access it outside of
the script.

Thanks for your help!

Jason

[Database Administrator]

Hello Jason,

It might look a little something like this

Public Sub Main()
'
Dim vs As Variables

'Lock the variable for writing
Dts.VariableDispenser.LockOneForWrite("FileCreationDate", vs)
'Get the file creation date
Dim dt As Date = System.IO.File.GetCreationTime(Dts.Connections.Ite m("f").ConnectionString)
'Assign to the variable
vs.Item("FileCreationDate").Value = dt
'Release the lock
vs.Unlock()

'
Dts.TaskResult = Dts.Results.Success
End Sub

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> Hello,
>
> I'm trying to use SSIS to import a flat file into my db. However, I
> want to also insert the date the flat file was created. I want to use
> the script task, but I'm new to VB.Net.
>
> Can someone please tell me how to do this? I've already spent over 12
> hours trying to get it work but to no avail.
>
> I believe I should use something like:
> File.GetCreationTime("C:\flatfile.txt"), but I don't know how to set
> up the code and assign the create date to a variable so I can access
> it outside of the script.
>
> Thanks for your help!
>
> Jason
>

[Database Administrator]

Thanks Allan! I appreciate your assistance.

I figured out a solution right after I posted my question. Here is what I
came up with that works:

---------------
Imports System.IO


Dim variables As Variables
Dim fileCreateDate As String
Dim fileName As String
If Dts.Variables.Contains("fileName") = True Then

Dts.VariableDispenser.LockOneForRead("fileName", variables)
fileCreateDate =
CStr(File.GetCreationTime(CStr(variables("fileName").Value)).ToString())
Dts.Variables("fileCreateDate").Value =
File.GetCreationTime(CStr(variables("fileName").Value)).ToString()

End If

----------------------
I don't know if it's right or wrong, but it works.

Do I need to unlock my variables as you did? Do you see anthing wrong with
my code? It's my first time writing in VB.Net.

What is the "f" in .Item("f").ConnectionString? Do you have to declard "f"
as a variable? I had an awful time with the Option Explicit and Option
Strict. I kept getting a "late binding" error.


Thanks
Jason



"Allan Mitchell" wrote:

> Hello Jason,
>
> It might look a little something like this
>
> Public Sub Main()
> '
> Dim vs As Variables
>
> 'Lock the variable for writing
> Dts.VariableDispenser.LockOneForWrite("FileCreationDate", vs)
> 'Get the file creation date
> Dim dt As Date = System.IO.File.GetCreationTime(Dts.Connections.Ite m("f").ConnectionString)
> 'Assign to the variable
> vs.Item("FileCreationDate").Value = dt
> 'Release the lock
> vs.Unlock()
>
> '
> Dts.TaskResult = Dts.Results.Success
> End Sub
>
> --
>
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
> > Hello,
> >
> > I'm trying to use SSIS to import a flat file into my db. However, I
> > want to also insert the date the flat file was created. I want to use
> > the script task, but I'm new to VB.Net.
> >
> > Can someone please tell me how to do this? I've already spent over 12
> > hours trying to get it work but to no avail.
> >
> > I believe I should use something like:
> > File.GetCreationTime("C:\flatfile.txt"), but I don't know how to set
> > up the code and assign the create date to a variable so I can access
> > it outside of the script.
> >
> > Thanks for your help!
> >
> > Jason
> >
>
>
>

[Database Administrator]

Hello Jason,

f is my Connection Manager name

Yes you must unlock the variable.

Do you lock for write the variable "fileCreateDate" on the Task itself?
If you do then I would be inclined to bring it inside the task or take the
"fileName" variable outside. What you have here is both methods in one task
and it may confuse.

ToString() will do your CStr()

Why is the name of the file in a variable? I would read from the connection
manager.

Your code will work fine.


--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> Thanks Allan! I appreciate your assistance.
>
> I figured out a solution right after I posted my question. Here is
> what I came up with that works:
>
> ---------------
> Imports System.IO
> Dim variables As Variables
> Dim fileCreateDate As String
> Dim fileName As String
> If Dts.Variables.Contains("fileName") = True Then
> Dts.VariableDispenser.LockOneForRead("fileName",
> variables)
> fileCreateDate =
> CStr(File.GetCreationTime(CStr(variables("fileName").Value)).ToString(
> ))
> Dts.Variables("fileCreateDate").Value =
> File.GetCreationTime(CStr(variables("fileName").Value)).ToString()
> End If
>
> ----------------------
> I don't know if it's right or wrong, but it works.
> Do I need to unlock my variables as you did? Do you see anthing wrong
> with my code? It's my first time writing in VB.Net.
>
> What is the "f" in .Item("f").ConnectionString? Do you have to
> declard "f" as a variable? I had an awful time with the Option
> Explicit and Option Strict. I kept getting a "late binding" error.
>
> Thanks
> Jason
> "Allan Mitchell" wrote:
>
>> Hello Jason,
>>
>> It might look a little something like this
>>
>> Public Sub Main()
>> '
>> Dim vs As Variables
>> 'Lock the variable for writing
>> Dts.VariableDispenser.LockOneForWrite("FileCreationDate", vs)
>> 'Get the file creation date
>> Dim dt As Date =
>> System.IO.File.GetCreationTime(Dts.Connections.Ite m("f").ConnectionSt
>> ring)
>> 'Assign to the variable
>> vs.Item("FileCreationDate").Value = dt
>> 'Release the lock
>> vs.Unlock()
>> '
>> Dts.TaskResult = Dts.Results.Success
>> End Sub
>> --
>>
>> Allan Mitchell
>> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com
>> |
>> http://www.konesans.com
>>> Hello,
>>>
>>> I'm trying to use SSIS to import a flat file into my db. However, I
>>> want to also insert the date the flat file was created. I want to
>>> use the script task, but I'm new to VB.Net.
>>>
>>> Can someone please tell me how to do this? I've already spent over
>>> 12 hours trying to get it work but to no avail.
>>>
>>> I believe I should use something like:
>>> File.GetCreationTime("C:\flatfile.txt"), but I don't know how to set
>>> up the code and assign the create date to a variable so I can access
>>> it outside of the script.
>>>
>>> Thanks for your help!
>>>
>>> Jason
>>>

[haripriya]

Hi
I want to read only latest three files landed in that folder. the folder is having so many files, but I have to take only latest three. I created foreachloop and reading the files, but I can able to read all the files in a folder.


Pls anyone help me how I can read only latest three files using scripts task or using foreachloop with expressions.

thanks in advance


venkat