Solved

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

Posted on 2013-10-23
3
2,203 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now