Fix 64-bit Dataflow Excel source/destination error (SSIS 2012 – EN-US)

While dealing with SSIS 2012, you might want configure connections to third party platforms, like SQL Server, Oracle DB or even Access or Excel inside your packages data flows. SSIS uses, what Microsoft called Connection Managers to configure this connections, and its easy to think about several scenarios in which your source or destination target could need to use the Excel Connection one, to select or insert data.

The problem this post wants to clarify is only applied to 64-bit environments, to which the Excel Connection manager isn´t compatible. This can be a problem in integration projects, and I speak for my self when I say that several of them, have an initial loading stage in which we get data from several Excel files and integrate them in a SQL Server database for instance.

Imagine that you have installed your SQL Server 2012 with Business Intelligence features in a 64 bit environment however you want to use an Excel Source inside your data flow. The following figure shows you how a simple data flow could manage this requirement.

When you compile and run a package like this, SSIS will try to execute it, by default, in a 64-bit version and you will get the following error:

[Read from source [16]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager “YOUR CONNECTION NAME” failed with error code 0xC0209303.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.

This error has I have previously explained, occurs because SSIS engine will try to run the package in a 64-bit version and the Excel Connection Manager is only compatible with 32-bits packages. The solution for this problem is very easy, go to the properties of your package (Right click in your project, inside the Solution Explorer – See the following figure).

 

Now go to the Configurations tab and inside it change the Run64BitRuntime property to False, which will force the project package to run in 32 bits. The configurations tab properties should now look like the following.

 

Although this solution won’t mess with any other Connection Manager inside your project, you must be aware that all your packages, inside this project will now run in 32-bits.

Thank you,

Rui Machado

Anúncios

One comment

  1. Thanks man,

    it really helped me. Hope to see more posts about SSIS and stuff like that in this blog. Maybe a tutorial, why not?

    Best regards and keep the good work.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s