SSIS: Integration Packages on 64bits

And so I’m back to designing SSIS packages. Its a fun activity really. SQL Server Integration Services is a really powerfull platform to move data around. It can read anything and write anywhere (and even if it can’t directly access something you can always call some 3rd party program to do it – but this is not the point of this post).

Back in SQL 2005 days I used to develop in a 32 bits machine, and the servers were also 32 bits. Not anymore: now everything is 64 bits. Well, except for Office. Office doesn’t like 64 bits (and neither does some Oracle clients, but again, not the point now). If you try to use an Excel connection it will work just fine while you design the package because BIDS run as a 32bits program at design-time, but at run-time it runs as 64bits and this may cause some weird errors when reading/writing to excel files.

There are some workarounds to make it work (in 2008R2 you have a flag inside the project/job properties named “Run64bitRuntime” / “Use 32 bit runtime”), but even doing this you may have some trouble. If you are just writing an output file the easiest and safest way would be to use a Flat File Destination and save the contents as a .CSV file.

Here is a cryptic message you may receive when using an Excel connection when you try to run your package:

[Excel Destination [62]] 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.

And here some more links on this subject:

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s