SSIS: Custom File Logging

I have tried to use the out-of-the-box logging feature provided by the SSIS package under SSIS > Logging.  However this do not allow me to log down some application-related information, such as file path, result count, etc.  As a result, I want to implement a custom file-based logging. 

After research, I find that there are two ways I can implement the custom loggin – use script task to write log to text file or implement your own Integration Services log provider.  For the sake of easy deployment, i choose to use script task to implement my custom file-based logging.  The followings are the steps I am going to do:

1. Create a log folder and assign the log file path
2. Standard log script task to write log
3. Uses event handler to log additional events such as OnError and OnWarning

First, create a log folder using File System Task together with File Connection.  Then concatenate the log file name to the variable in a script task:

Dts.Variables("SWIFTLogFilePath").Value = Dts.Variables("SWIFTLogFolder").Value.ToString() & _
                                          "\\swift" & Year(Now) & _
                                          IIf(Month(Now) < 10, "0", "").ToString() & Month(Now) & _
                                          IIf(Day(Now) < 10, "0", "").ToString() & Day(Now) & _
                                          ".log"

To remind, please ensure your variables are defined in ReadOnlyVariables / ReadWriteVariables. Then you can create another script task to write log to a file.   This is the sample code for writing a log file:

Public Sub WriteLog(ByVal logContent As String)
        Dim logWriter As StreamWriter

        Try
            logWriter = New StreamWriter(Dts.Variables("SWIFTLogFilePath").Value.ToString(), True)
            logWriter.WriteLine(logContent)
            logWriter.Close()
        Catch ex As Exception
            'Unexpected Exception
        End Try
    End Sub

Besides logging, i find that there is a trick/bug found in For Each Container.  You can see that I have duplicated to create log script task block in four different branches.  If I merge these four branches by pointing to the same log script task, the script task will not be executed.  Therefore it doesn’t support branch merging and runs tasks in sequential order only.

The last thing I want to show you is the Event Handler, you can implement the event handler of OnError and OnWarning to capture these information into your log file; just go to “Event Handlers” tab and select Executable and Event you want to handle. Then just put a script task as previous one, but additional information like System::ErrorCode, and System::ErrorDescription can be logged down as well.

SSIS project template missing

I have tried to create a SSIS project to import data from some flat files.  However, I cannot find any SSIS project template in Visual Studio 2005.  I have already installed VS 2005 and SQL Server 2005 in my machine.

Finally I figured out this problem, which is caused by the installation order of VS 2005 and SQL Server 2005.  If you have the same problem, you can fix this by following below steps:

1. Remove all SQL Server 2005 compoents first (which will remove SQL 2005 Express installed during VS 2005 setup)
2. Then reinstall SQL Server 2005 and ensure to select client compoents in installation options.
3. After success installation, you will find ”Business Intelligence Development Studio” under Programes > SQL Server 2005.  To remind, please ensure to install latest service patch for MS SQL Server 2005.

Hope your problem will be solved.

Mobile Development

I am recently working on a mobile project, so I have googled to get related materials and have a high-level pictures.  My basic requirement is to use Visual Studio 2008 to build up the solution and target to suppot BlackBerry, Windows Mobile and iPhone (those device supporting html).

Initially, I am looking to use Microsoft Mobile Library, which can help me to do rendering for different devices.  Below are resources to use Mobile library for develoment:

1. Mobile templates for VS 2008 (click here to download)
2. Emulators for Window Mobiles
   a. Install Active Sync and follow these setup steps.
   b. If you want more window mobiles emulators, you can install Windows Mobile 6 SDK Refresh and Windows Mobile 6 Localized Emulator Images.
3. Emulators for RIM BlackBerry
   a. Install BlackBerry Email and MDS Services Simulator (requires JDK1.5 or greater).
   b. Install any BlackBerry device simulator
4. Emulators for iPhone – there is no emulator for iPhone running on Windows platform.

After using Microsoft Mobile templates, I find that there have limitations on the use of mobile controls.  You cannot render friendly layout; as I target for html-support devices like Window Mobiles or iPhone, therefore I change to use standard asp.net web application for development.  However some points are required to take care:

1. ViewState – try to avoid using ViewState, in order to reduce the data sent throught the network.  This is also applied not to get data that are required on the page.
2. Screen size – make sure the elements on your page can be placed correctly, even the screen is small.
3. Input – use more user-friendly controls for input, instead of free text box.

Hello world!

Welcome to DM idea! This is my first blog and I would like to share i have learnt from my projects.  Also, i have planned to start to construct my own application framework by integrating existing out-of-the-box technologies.