Blog

In an earlier blog post I demonstrated how to map between hierarchical (XML) and relational (SQL) data using Altova MapForce. I also mentioned that MapForce can generate code (C#, C++, XSLT for instance) to execute the mapping in an environment outside MapForce. This being a blog mostly about SQL Server, one obvious question is how to run the generated mapping from SSIS.

I am currently working with a collegue in a MDS project where we are trying to run a MapForce mapping from SSIS. We have tried many different ways to run the MapForce generated code from SSIS, but no matter what we tried we got the error:

Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
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()
[/red_box]

Once we found out where to place the generated dll and exe files, and how to reference them from a SSIS script task it is pretty easy to run the MapForce mapping from SSIS.

In this demonstration I will use the same setup as in the earlier blog post about the basics in MapForce. The demonstration showed how to get data from a hierarchical XML document into a relational database. I will only use C# as the destination language. I am also assuming that Visual Studio is available. However, I’d guess that Visual Studio isn’t a requirement. The script for the database can be found here, and the XML file here.

Using the same mapping, it is easy to generate the code. Just select File -> Generate Code in -> C#. This will present you with a dialog to specify where the code will be placed. In the right lower corner in MapForce, there is the Messages window. If the code generation worked without errors, there will be a link to the Visual Studio Solution. Just click it to open the solution:

In Visual Studio, the solution looks like this:

The startup project, Mapping, is a console application. So to run the mapping, all you need to do is to build the solution and run Mapping.exe. Actually, Mapping.exe could be executed from SSIS with an Execute Process Task. But the downside to that is that you have no configuration possibilities. For instance regarding paths to files and connections to databases. Also it would be difficult to get any errors promoted to SSIS handling.

So what we wanted to do was to call the generated methods directly, by referencing them from a SSIS script task. There are a couple of ways to accomplish this. The obvious way is to add the assemblies to the GAC (Global Assembly Cache). But to do that, the assemblies needs to be signed. It’s easy to sign the projects in Visual Studio, but that would require modifying the MapForce generated code. And I wanted to come up with a solution where the generated code could be used without changes. The other method is to put the generated files in the DTS Binn folder. To make it work both in Visual Studio and in SQL Agent, the code should be put in both the 64-bit and the 32-bit versions. On my laptop, the 64-bit version is located in “C:\Program Files\Microsoft SQL Server\120\DTS\Binn”, and the 32-bit version is located in “C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn”. When you deploy to a server, you’d normally only need to place the code in the 64-bit folder.

One problem though. You may want to run many different MapForce mappings from SSIS on the same server. Each mapping will create its own code, but always with the same assembly names. The solution to this is to create a subfolder in the Binn folder, each corresponding to a unique mapping. In this example I created a folder Test\Invoices. To make the SSIS script task look in the subfolder, create the following ScriptMain method in the beginning of the class:

 static ScriptMain()
	{
		AppDomain.CurrentDomain.AppendPrivatePath(@"Test/Invoices");
	}

The “PrivatePath” needs to be located under the current “AppDomain”, so specifying a location somewhere else on disk won’t work:

If you have built the solution and put the generated files in DTS\Binn\Test\Invoices, you are ready to create the script task in SSIS:

The next step is to add references to Mapping.exe, Altova.dll and AltoveXML.dll. Right click References and select Browse:

Locate the files in the DTS\Binn\Test\Invoices folder (32-bit) as mentioned above. Select them and press OK:

Add a using Mapping statement in the beginning of the script:

Add the ScriptMain method as described above:

The next step is to copy some of the code from the generated Mapping project. Open the file MappingConsole.cs and copy the following rows:

Paste the code in the correct place in the SSIS script task window:

The path to the input XML file is relative in the generated code so change this to the complete path to the XML document, for instance C:\Temp\MapforceDemo\XMLInvoices.xml. To avoid problems with escape characters, the string must start with @. The complete row would look like this:

This is the complete code for the script:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Mapping;
#endregion

namespace ST_6a0e774f60d341b4902b120d0189f967
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
        static ScriptMain()
        {
            AppDomain.CurrentDomain.AppendPrivatePath(@"Test/Invoices");
        }

		public void Main()
		{
            MappingMapToXMLInvoices1 MappingMapToXMLInvoices1Object = new MappingMapToXMLInvoices1();
            {
                Altova.IO.Input XMLInvoices2Source = Altova.IO.StreamInput.createInput(@"C:\Temp\MapforceDemo\XMLInvoices.xml");

                MappingMapToXMLInvoices1Object.Run(
                XMLInvoices2Source,
                "Provider=SQLOLEDB.1; Data Source=YourServer\\YourInstance; Initial Catalog=XMLInvoices;Integrated Security=SSPI;");
            }

			Dts.TaskResult = (int)ScriptResults.Success;
		}

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

	}
}

Build the script and close the script task dialog and you’re done! Now you can run the mapping from SSIS. (Right click and choose Execute Task.) Check the destination tables in the demonstration database to see that out XML document has been inserted into the database:

The obvious next thing to do would be to replace the strings in the script task to variables or parameters, to make paths and connections strings more dynamic. Another thing to add would be proper error handling by using a try…catch block.

Note that MapForce add an extra \ in the connection string to databases if you use instances. So replace “Server\\Instance” with “Server\Instance” if you copy the connection string from the code.

One final note. If you still have problems with the UnsafeInvokeInternal error, a great tool to troubleshoot assembly loading issues is the Fusion Log Viewer tool (fuslogvw.exe). Run it as administrator. Read more about that tool and assembly loading failures here.