Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-10-23
3
Medium Priority
?
2,753 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:onesegun
  • 2
3 Comments
 
LVL 1

Expert Comment

by:kevwit
ID: 39822662
You still want help with this?
0
 

Accepted Solution

by:
onesegun earned 0 total points
ID: 39864848
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
 

Author Closing Comment

by:onesegun
ID: 39878977
No body got back with a solution so I created my own.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question