SQL SERVER 2012 Express version

Are you working on SQL Server 2005 or 2008 servers. Here is SQL Server 2012 Express version for download

Microsoft® SQL Server® 2012 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.

How to Validate Date based on format “MM/dd/YYYY” in VB.NET

Here is the sample code snippet which I wrote to do date validation for a particular format and culture.

   1: Private Function validateDateFormat(ByVal strDate As String) As Boolean

   2:     Dim isValid As Boolean = True

   3:     Dim provider As CultureInfo = CultureInfo.GetCultureInfo("en-us")

   4:     Dim formats As String = "d" '"MM/dd/yyyy"

   5:     Dim dateValue As DateTime

   6:     Try

   7:         If DateTime.TryParseExact(strDate, formats, provider, DateTimeStyles.None, dateValue) Then

   8:             isValid = True

   9:         Else

  10:             isValid = False

  11:         End If

  12:     Catch ex As Exception

  13:  

  14:     End Try

  15:     Return isValid

  16: End Function

You can change the formats and CultureInfo to your own custom information. Here is my pervious blog which describes how formatting date time can be done using string object.

How to Kill Excel Ghost Process in SSIS using VB.NET

Here is the simple method to kill Excel Ghost process which is running behind scene while running SSIS package. Here is a small method to kill Excel process if we know the excel caption or excel title we want to close.
Code snippet of the method to kill Excel process

Private Sub KillExcellProcess(ByVal excelCaption As String)
    Dim proc As System.Diagnostics.Process
    For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
        If (proc.MainWindowTitle = excelCaption) Then
            proc.Kill()
        End If
    Next
End Sub

Use this method in SSIS script task to kill the Excel Ghost process by calling this method in your code

 

Dim xlApp As Excel.Application
Dim xlBook As Excel.WorkbookClass
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlApp.Visible = False
xlApp.DisplayAlerts = False
' Once done with excel stuff and when you want 
' to kill excel use the below lines of code
Dim sCaption As String
sCaption = xlApp.Caption
KillExcellProcess(sCaption)

 

Hope this helps!!

How to Kill specific Excel Ghost Process generated in SSIS using VB.NET

There are some scenarios where we might need to kill excel process programmatically.  Even though we use proper error handling and try catch to clear all excel objects still the excel ghost process remains in usage. So for these cases we might need to kill the excel process generated in SSIS package by identifying its process.   Here is how we can kill specific excel process by getting the process id of the excel that is been generated by SSIS package in script task. Refer to my previous blog for kill process by its name

<Runtime.InteropServices.DllImport("user32.dll")> _
 Private Shared Function EndTask(ByVal hWnd As System.IntPtr) As Integer
 End Function
<Runtime.InteropServices.DllImport("user32.dll")> _
 Private Shared Function FindWindow(ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
 End Function
<Runtime.InteropServices.DllImport("kernel32.dll")> _
 Private Shared Function SetLastError(ByVal dwErrCode As Integer) As IntPtr
 End Function
<Runtime.InteropServices.DllImport("user32.dll", SetLastError:=True)> _
 Private Shared Function GetWindowThreadProcessId(ByVal hWnd As System.IntPtr, ByRef lpdwProcessId As Integer) As Integer
 End Function

 Public Sub EnsureProcessKilled(ByVal MainWindowHandle As System.IntPtr, ByVal Caption As String)
     SetLastError(0)
   ' for Excel versions <10, this won't be set yet
     If System.IntPtr.Equals(MainWindowHandle, System.IntPtr.Zero) Then
       MainWindowHandle = FindWindow(Nothing, Caption)
     End If
     If System.IntPtr.Equals(MainWindowHandle, System.IntPtr.Zero) Then
         Return
     End If
     ' at this point, presume the window has been closed.
     Dim iRes As Integer = 0
     Dim iProcID As Integer = 0
     iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
     ' can?t get Process ID
     If iProcID = 0 Then
        If EndTask(MainWindowHandle) <> 0 Then
             Return
         End If
         ' success
         Throw New ApplicationException("Failed to close.")
     End If
     Dim proc As System.Diagnostics.Process = Nothing
     proc = System.Diagnostics.Process.GetProcessById(iProcID)
     proc.CloseMainWindow()
     proc.Refresh()
     If proc.HasExited Then
         Return
     End If
     proc.Kill()
 End Sub

Use this method in SSIS script task to kill the Excel Ghost process by calling this method in your code

 

Dim sVer As String
sVer = xlApp.Version
Dim iHandle As IntPtr = IntPtr.Zero
If Val(Convert.ToDouble(sVer)) >= 10.0 Then
 iHandle = New IntPtr(CType(xlApp.Parent.Hwnd, Integer))
End If
'To kill process created by this job by process id
EnsureProcessKilled(iHandle, xlApp.Caption)

Hope this help!!

How do I format date value as yyyymmdd or any specific using SSIS expression builder?

I have a scenario where I need to create a folder on fly with current date in YYYYMMDD format.

Here is simple solution how we can achieve using Expression Builder in SSIS packages. Pick the variable that’s needs to be configured.

Go to properties of the variable you want to configure and click expression.

A Popup window will appear where you can select the variable you want to configure

Select the property you want to override. For me its Destination path property of the Source folder variable I am using in my package.

C:\Development\XXXXX\XXXXX\20130516\

Here is code that needs to append to the variable. So here is what I have used to get this in below format. 

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + 
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

 For me its yyyymmdd format if you want some other format just change the interval one of the following this as per your requirement

• yyyy – Year
• q – Quarter
• m – Month
• y - Day of year
• d – Day
• w – Weekday
• ww - Week of year
• h – Hour
• n – Minute
• s - Second

Hope this helps!!!