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.
(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.