SSIS Script Task writing the creation date and time of a file to a table

Hi All,

I'm an SSIS newbie and it's been a big learning curve for me to learn SSIS, Script Tasks, adding and passing variables to the Script Task etc in a short space of time.

Now I've partly achieved what I wanted i.e. to get the creation date and time of an Excel file using the code below (referencing the link I found on the Internet: http://www.bimonkey.com/2009/08/getting-file-information-with-the-script-task/

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<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
	

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts property. Connections, variables, events,
	' and logging features are available as members of the Dts property as shown in the following examples.
	'
	' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
	' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
	' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
	'
	' To use the connections collection use something like the following:
	' ConnectionManager cm = Dts.Connections.Add("OLEDB")
	' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
	'
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Help, press F1.

	Public Sub Main()

        ' String variable to hold file name
        Dim strFileName As String

        ' File name (including path) passed in from container
        strFileName = Dts.Variables("User::FileName").Value.ToString

        ' Create a System.IO.FileInfo object to retrieve the data
        Dim FileObject As System.IO.FileInfo = New System.IO.FileInfo(strFileName)

        ' Return the information via a message box
        'MsgBox("File name " + FileObject.FullName + " created on " + FileObject.CreationTime.ToString)
        MsgBox(FileObject.CreationTime.ToString)

    End Sub

    'Dts.TaskResult = ScriptResults.Success

End Class

Select allOpen in new window

The code gives me the creation date and time of an Excel file as seen in the attachment in a string format.

The next thing I want to do is to split the date and time, create a database table with a date field in date data type and a time field in time data type. Then pass the date and time from the string variable into this table.

Not sure how to approach it.

Your advice would be welcome.

Thanks,

OS