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

Open 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
onesegunAsked:
Who is Participating?
 
onesegunConnect With a Mentor Author Commented:
Hi Kevwit,

Thanks. But I've figured a way to do it. For the benefit of others here are the steps:


Create the script task to read the file properties for date and time
' 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

    Public Sub Main()

        ' String variable to hold file name
        Dim strFileName As String
        Dim strFileDate As String
        Dim strFileTime As String

        ' File name (including path) passed in from container
        strFileName = Dts.Variables("User::LastFile").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)
        'MsgBox(FileObject.CreationTime.ToShortDateString)
        'MsgBox(FileObject.CreationTime.ToShortTimeString)

        strFileDate = (FileObject.CreationTime.ToShortDateString)
        strFileTime = (FileObject.CreationTime.ToShortTimeString)

        'MsgBox(strFileDate)
        'MsgBox(strFileTime)

        Dts.Variables("User::FileDate").Value = strFileDate
        Dts.Variables("User::FileTime").Value = strFileTime

    End Sub

Open in new window


I basically pass the date and time to the two variables as seen above and put those variables as a read/write in the script task.

I create an expression in the SQL task as below using the date and time variables from the script task into a pre-created table with date and time fields.

"INSERT INTO tableA (Date, Time) SELECT CONVERT(Date,'"+ @[User::FileDate]+"') , '"+@[User::FileTime] +"'"

Open in new window


Thanks,

OS
0
 
kevwitCommented:
You still want help with this?
0
 
onesegunAuthor Commented:
No body got back with a solution so I created my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.