Executing geoprocessing tool in SSIS Package

[JeremyWaite]

I have a SQL Server 2008 R2 (64-bit) SSIS Package that I want to be able to execute geoprocessing tools inside of... We have done this is in the past, but with SQL Server 2005 (32-bit).

My script task bombs at this line: gp = CreateObject("esriGeoprocessing.GPDispatch.1")
Returns the error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: Cannot create ActiveX component.

I believe this is because we are using Windows Server 2008 R2 (64-bit) and ArcGIS is a 32-bit application. Can anyone confirm this? What can we do?

Thanks,
Jeremy

[ZachoryJones]

Has anyone responded to your issue?  I have the same need (Sql Server 2008 R2, ArcServer 10), to execute a geoprocessing tool using SSIS.  It took awhile to figure out, we were hoping to use the Web Service Task, but we weren't able to make that work.  We ended up using a script task.  I'll post the code that worked for us.  We are now migrating to 10.1 and I will need to modify the code a bit.  I was hoping there would be an easier way to do this besides a script task.  Let me know if you know of other ways.
            try
            {
                // Execute GeoProcessing Service from ArcServer
                string ToolName = (string)Dts.Variables["ArcServerToolName"].Value;

                com.name.gis.TOOLBOX_GPServer GPServer = new com.name.gis.TOOLBOX_GPServer();
                com.name.gis.GPValue[] gpValues = new com.name.gis.GPValue[2];
                GPServer.Execute(ToolName, gpValues, null, null);

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                Dts.Events.FireError(999, "Exception Occured", e.Message, String.Empty, 0);
                Dts.Log(e.Message, 0, null);
                Dts.TaskResult = (int)ScriptResults.Failure;