October 20, 2011 at 12:29 pm
Hi, I have a script task that reads a file's last modified date and compares it to the datetime in a SQL Server table to see if the file has already been loaded.
First step is to load an SSIS variable called LastLoadStamp from SQL Server. This works fine.
The next step is the script task.
Here is the Main method VB code for the script task:
Public Sub Main()
Dim infoReader As System.IO.FileInfo
infoReader = My.Computer.FileSystem.GetFileInfo("D:\test\jobscheduling.log")
If infoReader.LastWriteTime = Dts.Variables("LastLoadStamp").Value Then
Dts.TaskResult = Dts.Results.Failure
Else
Dts.Variables("CurrentStamp").Value = infoReader.LastWriteTime
MsgBox(Dts.Variables("CurrentStamp").Value)
Dts.TaskResult = Dts.Results.Success
End If
End Sub
I have a msgbox to display the output that it loaded into the CurrentStamp table. The output displayed in the msgbox is:
10/20/2011 10:55:58 PM
The next step is another Execute SQL task. It uses CurrentStamp as the parameter to do the table update.
For some reason, it writes 2011-10-17 22:55:00.000 to the table. Somewhere in the process, the seconds is being thrown out.
Does anyone know why that might be?
Thanks for reading.
Howard
edit: fixed typo in the time.
October 20, 2011 at 1:29 pm
What is the data type in the table? SmallDateTime's precision is to the minute and it rounds. See BOL here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2011 at 1:59 pm
Hi Jack,
The script task variable is type date and the ssis and sql server variables are type datetime.
It sure seems like the smalldate issue is what is happening but I'm not sure where the conversion is happening.
October 20, 2011 at 2:25 pm
I found that if I clear the table and insert the date/time from the variable then it is working. When I try to do it as an UPDATE of the existing single row then it isn't working.
The truncate/insert option works fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply