May 6, 2014 at 9:12 am
I'm developing a comprehensive SSIS solution for a client. Development is being done on a virtual machine (VMWare) that's a 32-bit environment.
The main gist of the project is that the SSIS package will be looking for and accessing / manipulating a high volume of Excel spreadsheets nightly. Most of the Excel spreadsheets are saved using the most recent version (MS Office 2013) of Excel. On the development machine, I've installed the Microsoft Access Database Engine 2010 Redistributable (32-bit version). I'm using the "Excel Connection Manager" to allow the package to connect to the numerous Microsoft Excel 2013 spreadsheets (.xlsx). Everything's working fine.
My connection string used for my "Source" and "Destination" Excel Connection Managers is basically:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyProject\ExcelFile.xlsx";Extended Properties="EXCEL 12.0 XML;HDR=YES";
On the client's 64-bit SQL Server machine where the finished solution will ultimately be installed, the machine is *NOT* running Microsoft Office. It's your "standard" situation I guess as far as a machine that's intended solely to run SQL Server and doesn't have any extra stuff like Microsoft Office / etc. installed on there.
The SSIS package will be called on the client's 64-bit machine via a SQL Server Agent job.
I've read and read forum posts online about the 32-bit/64-bit situation with SSIS and am frankly very confused as to what (if any) changes/obstacles I need to be prepared for once I ultimately install the finished solution on my client's 64-bit SQL Server machine.
Questions:
(1) The "Run64BitRuntime" property for my SSIS solution in my 32-bit development environment is currently set as "False". Does this need to be changed to "True" before I build/deploy the solution for installation on my client's 64-bit SQL Server machine?
(2) Again, I have the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable installed on my development machine. Once I install the finished solution on my client's 64-bit SQL Server machine, do I instead need to install the *64-bit* version of the Microsoft Access Database Engine 2010 Redistributable on the client's machine? Or, do I need to install the 32-bit Microsoft Access Database Engine 2010 Redistributable on the client's SQL Server machine? This is the most confusing point to me, as everything I read states that SSIS 2012 isn't a 64-bit application. Very confusing as to which (32-bit or 64-bit) Microsoft Access Database Engine 2010 Redistributable needs to be installed on the client's 64-bit machine with the conflicting info. I've read regarding 32/64-bit issues as related to SSIS.
Many thanks for reading through my detailed post/questions. I'm just trying to think ahead and make sure I'm going down the right path with my ongoing development as to allow the package that's running fine at present in my 32-bit development environment to continue to run successfully once it's installed on the client's 64-bit environment, has to still deal with Excel 2013 Excel files residing in that production 64-bit environment, etc.
May 6, 2014 at 10:14 am
1) The Run64BitRuntime property is a design time only property. You need to set it to false, since you are developing on a 32-bit environment and you have only the 32-bit ACE OLE DB provider installed.
2) You need to install the 64-bit ACE OLE DB provider on the server.
The runtime components of SSIS are available in 32-bit and in 64-bit. When people say SSIS is 32-bit only, they mean that the development environment SSDTBI - which is a Visual Studio shell - is 32-bit.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 6, 2014 at 11:18 am
I can't thank you enough for your very clear communication on what I should need to do with respect to the eventual installation on my client's 64-bit SQL Server machine! This info helps immensely. Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply