Call external Assembly in SSIS (and a peek at using FusionLogViewer.exe for assembly binding debugging)

1. Create a new class library in C# and replace the default Class1.cs by the code belowusing System;
   using System.Collections.Generic;
   using System.Text;namespace callssis

   { public class Class1{

   public string test()

   {return “I am external assembly”;}}}

2.  

   We will call this dll’s test function in SSIS

    

   1. Set the framework as 2.0 

      

       

   2. Sign it with a strong name 

      

       

   3. Open Visual studio command prompt and register this assembly in GAC 

      C:\Windows\system32>gacutil /i “C:\Users\ar11942\documents\visual studio 2010\Pr

       

      ojects\callssis\callssis\bin\Debug\callssis.dll”

       

      Output

       

      Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.1

       

      Copyright (c) Microsoft Corporation. All rights reserved.

       

      Assembly successfully added to the cache

       

   4. Create a new SSIS project . 

      1. Add a user variable foo and set it to “Before Call” 

         

          

      2. Create a script task and add this variable as Read/Write 

         

          

      3. Add reference to the callssis dll 

         

          

      4. Paste the following code in the script 

         using callssis;

          

         //and Main is

          

      public

      void Main()

       

      {

       

       

      // TODO: Add your code here

       

      Dts.TaskResult = (int)ScriptResults.Success;

       

       

      MessageBox.Show(new System.IO.FileInfo(@”C:\Users\ar11942\Documents\visual studio 2010\Projects\callssis\callssis\bin\Debug\callssis.dll”).Exists.ToString());

       

       

      //var assembly = Assembly.Load(@”C:\Users\ar11942\Documents\visual studio 2010\Projects\callssis\callssis\bin\Debug\callssis.dll”);

       

       

      //var myType = assembly.GetType(“Class1”);

       

       

      //var myMethod = myType.GetMethod(“test”);

       

      callssis.Class1 foo = new

      Class1();

       

       

      //object obj = Activator.CreateInstance(myType);

       

       

      //Dts.Variables[“foo”].Value = myMethod.Invoke(obj, null);

       

      Dts.Variables[“foo”].Value = foo.test();

       

       

      MessageBox.Show(Dts.Variables[“foo”].Value.ToString());

       

       

      }            

       

      1. Make sure the ssis script task’s properties are same as you dll (framework 2.0 in my case for both DLL and script task project ) Copy the external dll to C:\Program Files\Microsoft SQL Server\100\DTS\binn      or C:\Windows\Microsoft.NET\Framework\v2.0.50727\

          

          

      2. Run the package. If you see this , the call has worked  

         NOTE:-

          

         I was getting this exception at one point .

          

         SSIS package “Package.dtsx” starting.

          

         Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4’ or one of its dependencies. The system cannot find the file specified.

          

         File name: ‘callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4’

          

         at ST_a10608aff1784157b38ecee961f5cfbc.csproj.ScriptMain.Main()

          

         WRN: Assembly binding logging is turned OFF.

          

         To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

          

         Note: There is some performance penalty associated with assembly bind failure logging.

          

         To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

          

         — End of inner exception stack trace —

          

         at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

          

         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

          

         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

          

         at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

          

         at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

          

         Task failed: Script Task

          

         Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

          

         SSIS package “Package.dtsx” finished: Failure.

          

         If you get this then maybe your dll is not registered in GAC properly. Here are the steps I took

          

         1. Uninstall the assembly from GAC 
         2. Got to Visual Studio à SDK tools àFusion Log Viewer and run it . This can give you more description of assembly binding errors. 
         3. In order to make the FusionLogViewer work you need this in you registry so go to 

            HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion and create these

             

            

             

         4. In my case I pointed it to C:\temp\fusion but you can point it wherever you want. Then you can launch the FusionLogViewer.exe from the SKD tools I mentioned above and it will show you the errors. Here was mine 

         The operation failed.

          

         Bind result: hr = 0x80070002. The system cannot find the file specified.

          

         Assembly manager loaded from: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\mscorwks.dll

          

         Running under executable C:\Program Files\Microsoft SQL Server\100\DTS\binn\DtsDebugHost.exe

          

         — A detailed error log follows.

          

         === Pre-bind state information ===

          

         LOG: User = EUR\ar11942

          

         LOG: DisplayName = callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4

          

         (Fully-specified)

          

         LOG: Appbase = file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/

          

         LOG: Initial PrivatePath = NULL

          

         LOG: Dynamic Base = NULL

          

         LOG: Cache Base = NULL

          

         LOG: AppName = NULL

          

         Calling assembly : (Unknown).

          

         ===

          

         LOG: This bind starts in default load context.

          

         LOG: Using application configuration file: C:\Program Files\Microsoft SQL Server\100\DTS\binn\DtsDebugHost.exe.Config

          

         LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v2.0.50727\config\machine.config.

          

         LOG: Post-policy reference: callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4

          

         LOG: GAC Lookup was unsuccessful.

          

         LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis.DLL.

          

         LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis/callssis.DLL.

          

         LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis.EXE.

          

         LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis/callssis.EXE.

          

         LOG: All probing URLs attempted and failed.

          

          

   This is really useful when you are debugging external assembly bindings in any .Net project and after this I reinstalled the DLL and it worked.

    

   So hopefully you know

    

   1. How to call external (custom) assembly in SSIS 
   2. How to use FusionLogView.exe a great tool

Foreach() VS LINQ Cross Join and how to make SQLBulkCopy Faster

Are you using foreach loop to iterate over a collection and then make a datatable and send the query to SQLBulkCopy API.

Here is a faster way to do it in Linq

Recently I had a scenario where I had a set of values and a set of keys –

Values

ID	Values
Alpha	100

Beta	200
Gamma	300
Delta	400

keys 

-3

-4

1000

999

888

I Needed to create a datatable that looks like this 

-3	-4	1000	999	888	Alpha	100
-3	-4	1000	999	888	Beta	200
-3	-4	1000	999	888	Gamma	300
-3	-4	1000	999	888	Delta	400

Once I have this datatable I could pass it to SQLBulkCopy API and bulk insert these rows 

in SQL Server 2008 database.

The way the application was currently working was it was using a ForeachLoop which would 

iterate over each Values collection

and the one row of the Keys collection and create a datatable object. I was asked to perf 

tune it and after realising this 

was the bottleneck I used LINQ Cross Join and a EntityDataReader class mentioned below which 

you can download from code.msdn(link is

given below)

I have a small snippet that mimics the functionality and compares performance of both Foreach 

Loop in this scenarion and LINQ Cross JOIN

along with this EntityDataReader class.

First of all download this class from http://code.msdn.microsoft.com/LinqEntityDataReader and add 

it to your solution and use this

namespace in your code. Then copy paste this code in a console application .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Objects;
using Microsoft.Samples.EntityDataReader;

// Download this from http://code.msdn.microsoft.com/LinqEntityDataReader

namespace LINQCrossJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            //Simulate the Vectors and Context
            List<string> Vectors = new List<string>();
            List<string> Context = new List<string>();
            DataTable dt = new DataTable();
            dt.Columns.Add("Context");
            dt.Columns.Add("Vectors");
            
            for (int i = 0; i < 7500000; i++)
            {
                Vectors.Add(i.ToString());
            }
            Context.Add("Foo");
            //Simulate the Cross Join using Foreach
            Console.WriteLine("Started the Foreach Loop at {0}", DateTime.Now);
            foreach (var vector in Vectors)
            {
                dt.Rows.Add(Context, vector);

            }
            Console.WriteLine("Finished the foreach Loop at {0}", DateTime.Now);
            //Simulate Cross Join Using LINQ
            Console.WriteLine("Started the LINQ Loop at {0}", DateTime.Now);
            var combo = from C in Context
                        from V in Vectors
                        select new { C, V };
            IDataReader dr = combo.AsDataReader();
            //This dr can then be sent to the SQLBulkCopy API. These is a sample code to do so at 

            //http://code.msdn.microsoft.com/LinqEntityDataReader

            Console.WriteLine("Finished the LINQ Loop at {0}", DateTime.Now);
            Console.Read();

        }
       
    }
}

 

The output is

Total elapsed time in a foreach loop 25758 MilliSeconds

Total elapsed time in a LINQ and EntityDataReader loop 31 MilliSeconds

Performance optimization 830 times

Awesome !! In this scenario you are almost 1000 times faster . In my application with other bits of the code and

other bottlenecks like SAN IO , Network IO , SQL Server CPU bottleneck etc I could tune the application to go

from 200,000 rows / Sec to approx 2 Million rows/sec. I thought I will share the nugget with you guys.

 

2010 in review

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

The Blog-Health-o-Meter™ reads This blog is doing awesome!.

Crunchy numbers

A Boeing 747-400 passenger jet can hold 416 passengers. This blog was viewed about 1,600 times in 2010. That’s about 4 full 747s.

In 2010, there were 24 new posts, growing the total archive of this blog to 73 posts. There were 325 pictures uploaded, taking up a total of 24mb. That’s about 6 pictures per week.

The busiest day of the year was October 4th with 35 views. The most popular post that day was How can solve order effect your MDX calculations.

Where did they come from?

The top referring sites in 2010 were ssas-info.com, social.msdn.microsoft.com, sqlbits.com, linkedin.com, and sqldev.org.

Some visitors came searching, mostly for codeperfect limited, ashwani roy, codeperfect, generate ssis, and sp_makewebtask excel.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

How can solve order effect your MDX calculations October 2009

2

SSIS – Copy file when Exists and Not Locked by another process October 2010
2 comments

3

Parent-Child Dimensions – Introduction , drawback and alternative approach May 2009

4

Consulting July 2010

5

Writing C# Application on MAC OSX – Using MONO October 2010
2 comments

A simple introduction to event based programme

I am not getting into details of why we need events and how is this different from sequential program . events and event handlers are very common in a UI based programs but as most of the events and handlers come pre-coded we generally don’t think too much about them.

When it comes to communicating between objects then we think about them and in beginning it is a bit hard to get your head around them. So this is for a beginner to wants to get started with events. Code snippets at MSDN are also very good but I think this is a bit more simple and since it is in steps it is easy to comprehend of what is going on.

1. Create a new Type which inherits from EventArgs and this will be handler of information that you want to pass around

//Step 1 :- Create a type that inherits from event args and this will be used to pass the extra information from Publisher to subscriber
using System;

namespace CustomEventArgs
{
    public class NewInfoEventArgs : EventArgs
    {
// ReSharper disable InconsistentNaming
        protected readonly string _info;
// ReSharper restore InconsistentNaming

        public NewInfoEventArgs(string info)
        {
            _info = info;
        }
        public string Info
        {
            get { return _info; }
        }
    }

}

2. A publisher

        1. A public event Eventhanlder<T>

        2. A event invoker

       3. Actual method that does something

//2 Create a publisher 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CustomEventArgs;

namespace MultiThreading
{
    public class Publisher
    {
        //2.1 Add a new event hanlder
        public event EventHandler<NewInfoEventArgs> NewInfo;

        //2.2 Create a Method to invole this new event
        public void GetNewInfo(NewInfoEventArgs e)
        {
            EventHandler<NewInfoEventArgs> handler = NewInfo;
            if (handler != null) handler(this, e);
        }

        //2.3 Create a method that does something with the inputs and creates a desiered event
        public void CreateSomeEventBasedOnInput(string someMessage)
        {
            //take some message and put it in the event object . This info will be received later by subscriber 
            var newInfo = new NewInfoEventArgs(someMessage+DateTime.Now);

            GetNewInfo(newInfo);
        }
    }
}

3. A subscriber

    1. Ideally a constructor where you inject publisher and where it subscribes to publishers event

    2. A method wired to this event handler

//3. Create a subscriber 
   public class Subscriber
   {
       public Subscriber(Publisher pubs)
       {
           pubs.NewInfo +=PubsNewInfo;
       }

       private static void PubsNewInfo(object sender, NewInfoEventArgs e)
       {
           Console.WriteLine("This is received {0}", e.Info);
       }
   }

 

Testing it now

internal class Program
    {
        private static void Main(string[] args)
        {
            //Call them. Let publisher send events to subscriber which subscriber write to console.
            //Foo --> subscriber -- > foo+datetime --> comes to subscriber and it is written to console.
            var publisher = new Publisher();
            var subscriber = new Subscriber(publisher);
            for (int i = 0; i < 10; i++)
            {
                publisher.CreateSomeEventBasedOnInput("Foo");
                Thread.Sleep(1000);
                publisher.CreateSomeEventBasedOnInput("Bar");
                Thread.Sleep(1000);
            }

            Console.Read();
        }
    }

 

 

Output

This is received Foo09/12/2010 00:05:39

This is received Bar09/12/2010 00:05:40

This is received Foo09/12/2010 00:05:41

This is received Bar09/12/2010 00:05:42

This is received Foo09/12/2010 00:05:43

This is received Bar09/12/2010 00:05:44

This is received Foo09/12/2010 00:05:45

This is received Bar09/12/2010 00:05:46

This is received Foo09/12/2010 00:05:47

This is received Bar09/12/2010 00:05:48

This is received Foo09/12/2010 00:05:49

This is received Bar09/12/2010 00:05:50

This is received Foo09/12/2010 00:05:53

This is received Bar09/12/2010 00:05:54

This is received Foo09/12/2010 00:05:55

This is received Bar09/12/2010 00:05:56

This is received Foo09/12/2010 00:05:57

This is received Bar09/12/2010 00:05:58

This is received Foo09/12/2010 00:05:59

This is received Bar09/12/2010 00:06:00

Hello Reactive Extension !! Rx.Net getting started

Reactive extensions for .Net are an asynchronous event-based programming libraries. They use observable collections for doing so.

I have used Thread Pools, spawning my own threads, Aynsc using Begin and End Invoke pattern and also use Jeff Richter’s APM libraries to do Async programming. Guys who are used to using background worker class have been doing Aynsc programming for a long time already. Well , Why RX then. I don’t know yet. I have heard good things about it and I thought I will play with it a bit. So here it is

1. Download RX from http://msdn.microsoft.com/en-us/devlabs/ee794896.aspx

1. Start a new C# console application and add reference to System.Reactive. Please be aware that that you can use RX from Dot Net framework 3.5 onwards so your target framework should be 3.5 or higher . I have downloaded the 3.5 version of RX and using VS 2008 + dot net framework 3.5  so this is what I have in the project properties

 

 

3. Add reference to these libraries

 

1. Paste this code

using System;
using System.Linq;
using System.Threading;

namespace HelloRx
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var o = Observable.Start(() => { Console.WriteLine("Hello RX."); Thread.Sleep(3000); Console.WriteLine("Done."); });
            o.First();   // subscribe and wait for completion of background operation
        }
    }
}

1. 5. Hit F5 and you have written your first code using Reactive extensions

It is not only Size but Order matters as well

Mr DBA: – Why is your Index Creation Script so slow.
Mr DEV: – I don’t know it is very fast in Dev environment
Mr DBA: – Sure it is. Time taken to create indexes is a function of amount of data in table , table partitioning strategy , file groups and many more things that you don’t control plus one thing that you can .
Mr DEV: – What is it
Mr DBA: – Order of creating indexes. Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.
Mr DEV: – Ohh Ok !! I will rearrange my scripts to create clustered index first and others later.

SSIS – Copy file when Exists and Not Locked by another process

This is a very common scenario in SSIS where you want to check if source file exists. If it does then move it to certain folder otherwise Wait and check again.

You can do this using a script task and the following code

 

This is how you can configure your script task

 

 

Configure the READONLY and READWRITE variables

 

 

 

 

And in the script Put this code

‘ Microsoft SQL Server Integration Services Script Task

‘ Write scripts using Microsoft Visual Basic

‘ The ScriptMain class is the entry point of the Script Task.

 

Imports System

Imports System.Text

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

 

    ‘ The execution engine calls this method when the task executes.

    ‘ To access the object model, use the Dts object. Connections, variables, events,

    ‘ and logging features are available as static members of the Dts class.

    ‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    ‘

    ‘ To open Code and Text Editor Help, press F1.

    ‘ To open Object Browser, press Ctrl+Alt+J.

 

    Public Sub Main()

        ‘

        ‘ Add your code here

        ‘

        System.Threading.Thread.Sleep(1000)

        Dts.Variables("source_full_file_path").Value = New StringBuilder(Dts.Variables("source_folder_path").Value.ToString).Append("\").Append(Dts.Variables("source_file_name").Value.ToString).ToString()

        Dts.TaskResult = Dts.Results.Success

 

        If (File.Exists(Dts.Variables("source_full_file_path").Value.ToString()) And IsFileLocked(Dts.Variables("source_full_file_path").Value.ToString()) = False) Then

            Dts.Variables("file_exists").Value = True

        Else

            Dts.Variables("file_exists").Value = False

        End If

        ‘MsgBox(Dts.Variables("file_exists").Value)

        ‘MsgBox("file path: " + Dts.Variables("source_full_file_path").Value.ToString())

        ‘MsgBox("File Exists" + Dts.Variables("file_exists").Value.ToString())

 

    End Sub

    Public Function IsFileLocked(ByVal filename As String) As Boolean

        Dim Locked As Boolean = False

        Try

            Dim fs As FileStream = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.None)

            fs.Close()

        Catch ex As IOException

            Locked = True

        End Try

        Return Locked

    End Function

End Class

 

 

 

You can see that the last fuction IsFieLocked() is also there to avoid scernarios where file does exists but it is being written into by the extract from source. When this is happening you don’t want to try to move this file. Once the write Lock is release you can copy it.

 

 

After this Step you can do the file move in the same script task or you can use file system task to move/copy the file.

I have decided to use the file system task and below is how you can configure it .

 

 

Hope this helps someone

 

SSIs on SYBASE – Add column if doesnot exist on destination table

In my ssis Package I am trying to add a computed materialized column (similar to persisted computed columns in SQL Server ) and I wrote the code below to check if the column not already exists and Add it.

IF NOT EXISTS(SELECT id FROM syscolumns WHERE name = ‘trade_date’ AND OBJECT_NAME(id) = ‘fact_trade’)
BEGIN
PRINT ‘CREATE’
ALTER TABLE fact_trade
ADD trade_date AS CONVERT(DATETIME,CONVERT(VARCHAR,trade_date_key)) MATERIALIZED
END
ELSE
BEGIN
PRINT ‘ALREADY EXISTS’
END

It does not work. Even though the column does not exists it will still try to validate the query and fail it.

Workaround for this is :- Using a dynamic SQL to run encapsulating the T SQL script to ALTER your table.

IF NOT EXISTS(SELECT id FROM syscolumns WHERE name = ‘trade_date’ AND OBJECT_NAME(id) = ‘fact_trade’)
BEGIN
PRINT ‘CREATE’
EXEC(‘ALTER TABLE fact_trade
ADD trade_date AS CONVERT(DATETIME,CONVERT(VARCHAR,trade_date_key)) MATERIALIZED’)
END
ELSE
BEGIN
PRINT ‘ALREADY EXISTS’
END

This one will do the job for you.

SSIs on SYBASE – Add column if doesnot exist on destination table

In my ssis Package I am trying to add a computed materialized column (similar to persisted computed columns in SQL Server ) and I wrote the code below to check if the column not already exists and Add it.

IF NOT EXISTS(SELECT id FROM syscolumns WHERE name = ‘trade_date’ AND OBJECT_NAME(id) = ‘fact_trade’)
BEGIN
PRINT ‘CREATE’
ALTER TABLE fact_trade
ADD trade_date AS CONVERT(DATETIME,CONVERT(VARCHAR,trade_date_key)) MATERIALIZED
END
ELSE
BEGIN
PRINT ‘ALREADY EXISTS’
END

It does not work. Even though the column does not exists it will still try to validate the query and fail it.

Workaround for this is :- Using a dynamic SQL to run encapsulating the T SQL script to ALTER your table.

IF NOT EXISTS(SELECT id FROM syscolumns WHERE name = ‘trade_date’ AND OBJECT_NAME(id) = ‘fact_trade’)
BEGIN
PRINT ‘CREATE’
EXEC(‘ALTER TABLE fact_trade
ADD trade_date AS CONVERT(DATETIME,CONVERT(VARCHAR,trade_date_key)) MATERIALIZED’)
END
ELSE
BEGIN
PRINT ‘ALREADY EXISTS’
END

This one will do the job for you.

Grab MDX and automatically stuff parameters in it

I am sure we all have worked with SSRS parameterized reports firing MDX. And there is no way to test the query performance or results other than get the MDX from the profiler and then grab the XMLA and then substitute this manually.

Same issue cropped up on forums today and Darren Gosbell seems to have the perfect solution to automate this.

http://geekswithblogs.net/darrengosbell/archive/2007/07/04/ssas-exporting-mdx-from-profiler-part-2.aspx

Hope this helps some time for OLAP geeks.
Thanks Darren .:)