Moving an SSIS Package from a 32 bit machine to a 64 bit machine

I have an SSIS package that works on a 32 bit server but fails on a 64 bit machine with a number of errors, the most interesting being:

[Excel Destination [511]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

  

Surfing I found these sites...

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9

http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html

It turns out that there are a number of drivers that are available in 32 bit but are not available in 64 bit. It is possible to run an SSIS package in 32 bit on a 64 bit machine. These URLs suggest

The first thing is to make the Visual Studio 2008 DTS package run in 32 bit mode in the designer. This can be done by:

1. Goto the properties of the SSIS project 
2. In the left tree view select Configuration Properties
3. Set Run64BitRuntime = false

This enables you to debug the SSIS package. The next step is to execute the package in 32 bit. The easiest approach is to use

DTExec /f "D:\MyPath\MySSISPackage.dtsx" /SET \package.Variables[Variable1].Value;"Value1" /SET \package.Variables[Variable2].Value;"Value2" /X86

This works well if the total length of the command line is less than 255 characters. In my case I had a lot of parameters.

To get arround this 255 caharacter limitation I think the best solution is to make a command line executable that is compiled in 32 bit. I thought about making a power shell command for this but decided it would be simpler to keep it to a command line and search the standard output for "Success" or "Failure".

To get this to work I included the following DLLs:

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Dts.Design.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll

D:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

The code looked like:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Threading;

namespace Ssis32BitExec
{
class Program
{
static void Main(string[] args)
{

if (args.Count() != 2)
{
Console.WriteLine("Ssis32BitExec ");
Console.WriteLine("===========================================");
Console.WriteLine("");
Console.WriteLine("Utility to execute an SSIS package in 32 bit mode thus enabling 32 bit drivers");
Console.WriteLine("that may not be available in 64 bit");
Console.WriteLine("");
Console.WriteLine("");

Console.WriteLine(" UNC path to .dtsx file (SSIS package)");
Console.WriteLine(" Path to parameter name and value pair file (This file");
Console.WriteLine(" contains lines like ParameterName;ParameterValue)");

Console.ReadLine();
Environment.Exit(0);
}

string packagePath = args[0];
string parameterValuePairsPath = args[1];

Console.WriteLine("SizeOf IntPtr is: {0}", IntPtr.Size);

if (!File.Exists(packagePath))
{
Console.WriteLine("Failed: The following file does not exist {0}", packagePath);
Environment.Exit(0);
}

if (!File.Exists(parameterValuePairsPath))
{
Console.WriteLine("Failed: The following file does not exist {0}", parameterValuePairsPath);
Environment.Exit(0);
}


XmlDocument packageDoc = new XmlDocument();
packageDoc.Load(packagePath);

Package package = new Package();
package.LoadFromXML(packageDoc.DocumentElement, null);


string[] lines= File.ReadAllLines(parameterValuePairsPath);
string[] Fields;
foreach(string line in lines)
{
Fields = line.Split(';');
if (Fields.Count() != 2)
{
Console.WriteLine("Failed: The following line is not correctly formated {0}", line);
Thread.CurrentThread.Abort();
}
if (package.Variables.Contains(Fields[0]))
{
package.Variables[Fields[0]].Value=Fields[1];
}
}

//This is a fudge factor that allows a badly constructed package to throw some errors and run through
//package.MaximumErrorCount = 2;


DTSExecResult result = package.Execute();

if (result == DTSExecResult.Success)
{
Console.WriteLine("Success");
Environment.Exit(0);

}
else
{

Console.WriteLine("Failed");
for (int i = 0; i < package.Errors.Count; i++) { Console.WriteLine(package.Errors[i].Description); } Environment.Exit(1); } } } }

To know for sure that this is running in 32 bit the Console.WriteLine("SizeOf IntPtr is: {0}", IntPtr.Size); equals 4 for 32 bit and 8 for 64 bit.

I call this from within VB.NET in:


Dim TempFilePath As String = IO.Path.GetTempFileName
Dim Sw As New StreamWriter(TempFilePath)
Sw.WriteLine("Param1;{0}", Value1)
Sw.WriteLine("Param2;{0}", Value2)
Sw.Close()

Dim SSIS32BitProcess As New ProcessStartInfo()
SSIS32BitProcess.WorkingDirectory = Environment.CurrentDirectory
SSIS32BitProcess.FileName = "Ssis32BitExec.exe"
SSIS32BitProcess.UseShellExecute = False
SSIS32BitProcess.RedirectStandardOutput = True
SSIS32BitProcess.RedirectStandardError = True
SSIS32BitProcess.Arguments = packagePath + " " + TempFilePath

Dim Proc As Process = Process.Start(SSIS32BitProcess)

Dim Out As String = Proc.StandardOutput.ReadToEnd()
Proc.WaitForExit()

Dim Err As String = Proc.StandardError.ReadToEnd()
Proc.WaitForExit()

If Out.Contains("Success") Then
Return True
Else
Message = Out + vbCr + Err
Return False
End If
File.Delete(TempFilePath)
Return True