Month: February 2010

When you are on a x64 machine and de business intellegence studio doesn’t stop the debugger in you’re script task on the break points, set you’re package to x86 (Run64BitRuntime = False) to start debugging the package.

Make sure the package is selected en not the individual task (then the debugger also won’t stop).

 

If you reference an custom none GAC assembly from you’re SSIS 2008 script task, you can get an exception, stating the assembly could not be found.

Error

Error: 0x1 at Filesysteem opschoning: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘Ada.Cdf, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Ada.Cdf, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’
   at ST_124cffcdeb3f407fa68f54b9192c0cdf.csproj.FileSystemCleaner.Clean()
   at ST_124cffcdeb3f407fa68f54b9192c0cdf.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.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature 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 System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Filesysteem opschoning
Warning: 0x80019002 at OpschoonProces: 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 "OpschoonProces.dtsx" finished: Failure.

 

Solution

To prevent this error, I use the AppDomain AssemblyResolve event to add a folder to the .NET assembly probing (search) path:

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Reflection;
using System.IO;

namespace ST_124cffcdeb3f407fa68f54b9192c0cdf.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

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

        To open Help, press F1.
    */

        public void Main()
        {
            // Add bin folder to .NET assemlby search path
            this.BindAssemblyResolveEventHandler();

            FileSystemCleaner cleaner = new FileSystemCleaner();
            cleaner.Clean();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        /// <summary>
        /// Add an evenhandler for adding a folder to the .NET assemlby search (probing) path.
        /// </summary>
        public void BindAssemblyResolveEventHandler()
        {
            var currentDomain = AppDomain.CurrentDomain;
            currentDomain.AssemblyResolve += LoadAssemlbyFromProductInstallationFolder;
        }
        /// <summary>
        /// This function is called when the .NET runtime searches for an assemlby to load and can't find that assembly in the current search path.
        /// The current search path includes "bin folder application", the global assemlby cache, system32 folder etc.
        ///
        /// This function adds a folder to the current search path at runtime.
        ///
        /// An assembly can be a dll or exe, the ResolveEventArgs argument does not cotain this information.
        /// The code will first check if a dll exist in the given folder, if found it loads the dll.
        /// If the dll is not found, the code checks if an executable exists in the given folder, if found it loads the exe.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public Assembly LoadAssemlbyFromProductInstallationFolder(object sender, ResolveEventArgs args)
        {
            Assembly result = null;
            if (args != null && !string.IsNullOrEmpty(args.Name))
            {
                var folderPath = GetExecutionPath();
                var assemblyName = args.Name.Split(new string[] { "," }, StringSplitOptions.None)[0];
                var assemblyExtension = "dll";
                var assemblyPath = Path.Combine(folderPath, string.Format("{0}.{1}", assemblyName, assemblyExtension));
                if (!File.Exists(assemblyPath))
                {
                    assemblyExtension = "exe";
                    assemblyPath = Path.Combine(folderPath, string.Format("{0}.{1}", assemblyName, assemblyExtension));
                }

                result = Assembly.LoadFrom(assemblyPath);
            }

            return result;
        }
        /// <summary>
        /// Get the current execution path
        /// </summary>
        /// <returns></returns>
        public string GetExecutionPath()
        {
            return new Uri(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase)).LocalPath;
        }
    }
}

If you get a warning like:

Warning: 0x7 at Filesysteem opschoning: Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime.

when trying to debug a SSIS package, change the project property [Run64BitRuntime] to False: