Friday, August 12, 2011

SSIS Process files that has been downloaded in the last hour

A recent question on SQL Server Central was asking about how to process the last hour downloaded files.

I think it's fairly simple using a script task to get the files creation date property, and then added a constant constraint to check the difference between now and the creation date, then pass through to the data flow task to process the file.


I've created 6 variables, I'm here getting more usable file properties, but you don't have to do that, I'm just here showing the concept more than doing the exact time difference.

Add a "foreachloop Container" and a "script task" inside.


edit the "Foreachloop Container" location to your file system location. (the one that has your files).


Map the 'FilePath' variable to the output of the "foreachloop"




Edit the "Script Task" ,I'm using VB.net language here.



Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO.File
Imports System.IO.FileInfo
<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()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing  'var dispenser
        Dts.VariableDispenser.LockForRead("User::FilePath")
        Dts.VariableDispenser.LockForWrite("User::FileSize")
        Dts.VariableDispenser.LockForWrite("User::FileCreationDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate")
        Dts.VariableDispenser.LockForWrite("User::FileAge")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim f As IO.FileInfo = New System.IO.FileInfo(CStr(Vars("User::FilePath").Value.ToString))
        Dim fileSize As Double = f.Length
        Dim fileDate As Date = f.CreationTime
        Dim fileLastAccess As Date = f.LastAccessTime
        Dim fileLastMod As Date = f.LastWriteTime
        Vars("FileSize").Value = fileSize / 1024   'get file size
        Vars("FileCreationDate").Value = fileDate
        Vars("FileLastAccessedDate").Value = fileLastAccess
        Vars("FileLastModifiedDate").Value = fileLastMod
        Vars("FileAge").Value = CInt(DateDiff(DateInterval.Minute, fileDate, Now()))
        'only for testing to visualize the results, comment out
        MsgBox("File Name: " & f.FullName & vbCrLf & _
                "File Size: " & Vars("FileSize").Value.ToString & vbCrLf & _
                "File Date: " & Vars("FileCreationDate").Value.ToString & vbCrLf & _
                "File Age: " & Vars("FileAge").Value.ToString)

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

That's it....
Test it and you'll get results like that..





Now use a  precedence constraint to check the file's age and process the less than 60 minutes.

The package could be downloaded from: http://www.box.net/shared/dgyb5z79bgdgdp9pkpi3
Hope that helps someone...

Wednesday, August 3, 2011

SSRS Data-driven subscription E-mail is not an option

Like the title is saying, when you attempt to create a data-driven subscription in SSRS, E-mail is not one of the listed options.


That means you've not configured a SMTP server yet !

Start -> All Program -> Microsoft SQL Server 2008 / R2 -> Configuration Tools -> Reporting Service Configuration Manager


Then the option will be Appear as normal.


Hope that helps.

Monday, August 1, 2011

Recent Horrible story with my toshiba Laptop!!!

Recently I bought a Toshiba laptop from best buy and that was my biggest mistake, everybody warned me from Toshiba but I didn't listen....  the hard drive crashed after 3 months and it was a nightmare over the phone with them for hours so they can understand what I'm trying to say !!!!!

and the guy over the phone kept asking "did you pack your data before shipping the laptop" and he's processing a repair for a crashed hard drive...

Also to mention that you'll have to pay $25 so they can provide you with a UPS box and pre-paid shipping label.

The whole process took 2 weeks to call, send and receive.....

The bottom line that they're cheap company that doesn't stand by their product and by their customers, they dont' care about the customer to leave them without a laptop for 3 weeks!!

and you pay to get your stuff fixed while it's still under the warranty !!

That's only my personal experience and I'm sharing it for knowledge....