Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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