Archive

Archive for June, 2011

SSIS Tips & Tricks Best Practice

June 27, 2011 1 comment

I’ve been developing SSIS for quite sometime now and below are some tips and tricks I’ve used to make solution to be more consistent, fast and efficient.

1. Shape names (tranform shapes), use prefix, I use a 3 letter acronym. For ex. Execute SQL Task name it like EST Update Status.

2. Do use  Sequence Container to group related task/shapes.

3. Variables 

3.1 Limit the scope, create the variables closer to the shape that would use it.

3.2 Use Pascal casing for variables that will be configurable in dtsConfig and camel casing for the rest. This will make your life easier, by the time you’re configuring the dtsConfig it will be easy to identify which ones are to be included.

3.3 Variable access – instead of manually adding ReadOnlyVariables and ReadWrite Variables on the Script Task, you can use the script below to access and set the values of the variables without having to encounter the nasty variable is locked error.

Private Function GetValue(ByVal variableName As String) As Object
Dim var As Variables
Dim objVal As Object
Dim varName As String = "User::" & variableName
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(var)
objVal = var(varName).Value
var.Unlock()
GetValue = objVal
End Function

Private Sub SetValue(ByVal variableName As String, ByVal value As Object)
Dim var As Variables
Dim varName As String = "User::" & variableName
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(var)
var(varName).Value = value
var.Unlock()
End Sub
End Class

4. Error Handling –  Use precedance constraint to handle failure instead of using Event Handlers.  The reason for this is that, it’s more simple, clean and easy to spot. 🙂 Also, sometimes using event handler will give you weird cryptic error that is both waste of time and unnerving.

5. Package properties–  Set the Protectionlevel to DontSaveSensitive and SuppressConfigurationWarning to True.

Setting the protection level will save you from:

Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

this is assuming that you are using Integrated Security or will going to use SQL Configuration Table see Item# 8.

SuppressConfigurationWarning, just like what the property indicates. This maybe because the location of dtsConfig in your production is different from your virtual or dev machine.

6. Bulk Update – You might be tempted to use a OLEDB Command to perform an update, but this is the slowest possible way since it means that this command will be executed multiple times based on the total number of rows.

Faster way is to create a temporay table on the target database, populate that table then do an execute sql task performs the update. Aside from the normal db_datareader/writer permission you’ll need a db_ddladmin permission to drop and create tables.

UPDATE d
d.Column1 = t.Column1,
d.Column2 = t.Column2
FROM DestinationTable d
INNER JOIN TmpTable t ON (d.Key = t.Key)

7. For parallel data processing you can use the Balanced Data Distributor for SSIS, this component can be downloaded seperately from
Microsoft.

For the quick demo click here.

To download click here

8. If the number of SSIS projects will grow overtime especially when connecting to different sources maintaining the connectionString will be hard if not centralized. To implement centralize configuration for SSIS see this article:  https://randypaulo.wordpress.com/2011/12/02/ssis-centralize-connection-repository/

Lastly, make sure that connecting lines between task is straight :), it just look more professional and nice if it is.

Happy coding..

Advertisements

C# using ThreadPool for multi-threaded application

June 21, 2011 1 comment

I’m currently designing an application specifically to handle multiple processing at a certain time. I’ve read all articles about threading, whether to use a background worker, custom thread management, or by using a thread pool. What facinates me is the thread pool, just by passing a workitem viola you have a working multi-threaded application. See simple example below:


using System;
using System.Threading;

public class MyProcess
{

    private ManualResetEvent _doneEvent;

    public MyProcess(ManualResetEvent doneEvent)
    {
        _doneEvent = doneEvent;
    }

    public void MyProcessThreadPoolCallback(Object threadContext)
    {
        int threadIndex = (int)threadContext;
        Console.WriteLine("thread {0} started...", threadIndex);
        StartProcess();
        Console.WriteLine("thread {0} end...", threadIndex);

    // Indicates that the process had been completed
        _doneEvent.Set();
    }

    public void StartProcess()
    {
        // TODO: Add code for processing here

    }

  
}

public class ThreadPoolExample
{
    static void Main()
    {
        const int totalCountToProcess = 10;
        
        ManualResetEvent[] doneEvents = new ManualResetEvent[totalCountToProcess];
        MyProcess[] MyProcessArray = new MyProcess[totalCountToProcess];
      
        // Configure and launch threads using ThreadPool:
        Console.WriteLine("launching tasks...");
        for (int i = 0; i < totalCountToProcess ; i++)
        {
            doneEvents[i] = new ManualResetEvent(false);
            MyProcess p = new MyProcess(doneEvents[i]);
            MyProcess[i] = p;
            ThreadPool.QueueUserWorkItem(p.MyProcessThreadPoolCallback, i);
        }

        // Wait for all threads in pool to finished processing

        WaitHandle.WaitAll(doneEvents);
        Console.WriteLine("All Process are complete.");

           }
}

 

Categories: .NET Development Tags: ,

Storing files to mssql database and downloading using Asp.NET

June 21, 2011 2 comments

1) First create a new column and set it as varbinary(MAX) for the datatype.

2) Use this code to store the file to database:

            //Read File to Bytes
                    FileStream st = new FileStream(<Location of the file>, FileMode.Open);
                    byte[] fileData= new byte[st.Length];
                    st.Read(fileData, 0, (int)st.Length);
                    st.Close();

                    SqlParameter[] param = new SqlParameter[] { new SqlParameter(“@ID”,<Reference ID>),
                                                    new SqlParameter(“@Data”,fileData)  };

            int i = SqlAccessor.ExecuteNonQuery(<ConnectionString>, SqlAccessor.SqlCommandBuilder(new SqlCommand (<Stored Procedure Name>), param), CommandType.StoredProcedure, out retVal);

 I’ve used Application Blocks to simplify data operation.

To Retrieve the data and to be downloaded from an ASP.NET Page:

3)  I created a function that returns a byte array with ID as parameter

 public static byte[] GetFileFromDB(string id)
    {
        object[] retVal = null;
        byte[] file = null;
        SqlParameter[] param = new SqlParameter[]
        {
            new SqlParameter (“@id”, id)
        };
        string _connString = ConfigurationManager.ConnectionStrings[<ConnectionStringName>].ConnectionString.ToString();
        SqlDataReader reader = SqlAccessor.ExecuteReader(_connString, SqlAccessor.SqlCommandBuilder(new SqlCommand(“SourceFileLogSelFileDataProc”), param), CommandType.StoredProcedure, out retVal);

        if (reader.Read())
        {
            file = (byte[])reader[“<varbinary column that we define in Step 1>“];
        }

        return file;
    }

4) And on the ASP.NET Page itself, on the Page_Load event:

           byte[] fileData = Utils.GetFileFromDB(<ID>);
            Response.ClearContent();
            Response.AddHeader(“Content-Disposition”, “attachment; filename=” + <filename>);
            BinaryWriter bw = new BinaryWriter(Response.OutputStream);
            bw.Write(fileData);
            bw.Close();
            Response.ContentType = ReturnExtension(<file extension>);
            Response.End();

 5) I have another method to determine the ContentType:

  private string ReturnExtension(string fileExtension)
    {
        switch (fileExtension)
        {
            case “.htm”:
            case “.html”:
            case “.log”:
                return “text/HTML”;
            case “.txt”:
                return “text/plain”;
            case “.doc”:
                return “application/ms-word”;
            case “.tiff”:
            case “.tif”:
                return “image/tiff”;
            case “.asf”:
                return “video/x-ms-asf”;
            case “.avi”:
                return “video/avi”;
            case “.zip”:
                return “application/zip”;
            case “.xls”:
            case “.csv”:
                return “application/vnd.ms-excel”;
            case “.gif”:
                return “image/gif”;
            case “.jpg”:
            case “jpeg”:
                return “image/jpeg”;
            case “.bmp”:
                return “image/bmp”;
            case “.wav”:
                return “audio/wav”;
            case “.mp3”:
                return “audio/mpeg3”;
            case “.mpg”:
            case “mpeg”:
                return “video/mpeg”;
            case “.rtf”:
                return “application/rtf”;
            case “.asp”:
                return “text/asp”;
            case “.pdf”:
                return “application/pdf”;
            case “.fdf”:
                return “application/vnd.fdf”;
            case “.ppt”:
                return “application/mspowerpoint”;
            case “.dwg”:
                return “image/vnd.dwg”;
            case “.msg”:
                return “application/msoutlook”;
            case “.xml”:
            case “.sdxl”:
                return “application/xml”;
            case “.xdp”:
                return “application/vnd.adobe.xdp+xml”;
            default:
                return “application/octet-stream”;
        }
    }

Hope this helps.

Categories: .NET Development Tags: ,

System errorcode 53 while connecting to an existing network share drive

June 21, 2011 Leave a comment

Recently I have encountered the famous error 53 (network path not found) while connecting to network share folder using C# WinAPI. So typically first step is to really check whether path is existing with valid credentials and yes it was there and the credential is valid. After one hour of checking an rechecking I discovered that the path (folder) was a DFS (Domain based File Share).

Resolution: Get the actual folder path from the referral list in DFS tab of the shared path

.

Categories: .NET Development Tags: , ,
%d bloggers like this: