May 1, 2014 at 1:20 pm
I'm working on a project writing a complex SSIS package for a client. The virtual machine I'm using for the development environment is running the 32-bit version of Windows 7, and the 32-bit version of SQL Server 2012 Enterprise.
Once I'm finished with development and am ready to install the SSIS package on the client's SQL Server machine, this machine is running a 64-bit operating system and of course the 64-bit version of SQL Server 2012.
I'd like to get input from anyone as far as any special considerations I need to be thinking about at present that might cause installation difficulty going between a development environment that's 32-bit in nature to an installation/production environment that's 64-bit in nature.
To give you a little more insight as to the general nature of the SSIS package, it will basically be looping through a folder once nightly to determine if customers have uploaded Excel files into this location. The SSIS job will then go through each of the Excel files found and do ETL on them & create new individual Excel files with the original data transformed into a consistent layout preferred by my client so all of the files end up looking the same aside from of course different data in each file. Most of the Excel files coming in from customers are going to be from fairly recent version of Excel, so I've installed the Microsoft Access Database Engine 2010 Redistributable on my development environment's virtual machine (the 32-bit version... AccessDatabaseEngine.exe).
I'm assuming I'll need to install the 64-bit (AccessDatabaseEngine_x64.exe) Microsoft Access Database Engine 2010 Redistributable on my client's 64-bit SQL Server machine once I'm ready to do the installation of the finished SSIS job?
I'm still somewhat new to SSIS development and the 32-bit development vs. 64-bit production environment issue started making me wonder if there are any challenges I'll face once I'm ready for the production location installation of the finished SSIS job.
Many thanks in advance for any advice / recommendations any of you may have.
May 1, 2014 at 1:29 pm
brad.mccollum (5/1/2014)
To give you a little more insight as to the general nature of the SSIS package, it will basically be looping through a folder once nightly to determine if customers have uploaded Excel files into this location. The SSIS job will then go through each of the Excel files found and do ETL on them & create new individual Excel files with the original data transformed into a consistent layout preferred by my client so all of the files end up looking the same aside from of course different data in each file. Most of the Excel files coming in from customers are going to be from fairly recent version of Excel, so I've installed the Microsoft Access Database Engine 2010 Redistributable on my development environment's virtual machine (the 32-bit version... AccessDatabaseEngine.exe).
Others here know far more about this than I do, but here is a suggestion:
Make sure the packages using Excel sources/destinations are configured to run AS 32-bit packages. If you're using Excel as a source or destination then you have to do this; I've never seen a workaround for it. I think there are a couple of others that require a 32-bit environment too, but most don't.
If I'm wrong I am 100% positive someone here will pick it up. 😀
May 20, 2014 at 5:17 pm
In SSIS 2008, you can right click the Project (top node) in the VS solution explorer. From there go to Properties , Debugging , Run64BitRuntime... set this property to false. To run Excel, I'm sure you will of course need Excel installed on the client machine. See if it is similar in 2012.
----------------------------------------------------
May 20, 2014 at 6:33 pm
Turns out u don't have to have Excel installed on the client machine, only the Microsoft Acess 2010 redistributable.
May 20, 2014 at 11:40 pm
SQL is delicious (5/1/2014)
brad.mccollum (5/1/2014)
To give you a little more insight as to the general nature of the SSIS package, it will basically be looping through a folder once nightly to determine if customers have uploaded Excel files into this location. The SSIS job will then go through each of the Excel files found and do ETL on them & create new individual Excel files with the original data transformed into a consistent layout preferred by my client so all of the files end up looking the same aside from of course different data in each file. Most of the Excel files coming in from customers are going to be from fairly recent version of Excel, so I've installed the Microsoft Access Database Engine 2010 Redistributable on my development environment's virtual machine (the 32-bit version... AccessDatabaseEngine.exe).
Others here know far more about this than I do, but here is a suggestion:
Make sure the packages using Excel sources/destinations are configured to run AS 32-bit packages. If you're using Excel as a source or destination then you have to do this; I've never seen a workaround for it. I think there are a couple of others that require a 32-bit environment too, but most don't.
If I'm wrong I am 100% positive someone here will pick it up. 😀
I'll pick it up.
There is a 64-bit ACE OLE DB provider, so you can run a package with Excel source/destination perfectly on a 64-bit machine.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 20, 2014 at 11:42 pm
MMartin1 (5/20/2014)
In SSIS 2008, you can right click the Project (top node) in the VS solution explorer. From there go to Properties , Debugging , Run64BitRuntime... set this property to false. To run Excel, I'm sure you will of course need Excel installed on the client machine. See if it is similar in 2012.
The Run64bitRuntime property only works during design time (it is a visual studio property).
If you want a package to run in 32 bit on the server, you explicitly have to tell it to do so. You can do this by either launching the 32-bit version of DTEXEC, or by selecting a checkbox in the SQL Server Agent jobstep.
Office itself does not have to be installed on the server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 20, 2014 at 11:42 pm
brad.mccollum (5/20/2014)
Turns out u don't have to have Excel installed on the client machine, only the Microsoft Acess 2010 redistributable.
That is correct.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 21, 2014 at 7:19 am
Koen Verbeeck (5/20/2014)
SQL is delicious (5/1/2014)
brad.mccollum (5/1/2014)
To give you a little more insight as to the general nature of the SSIS package, it will basically be looping through a folder once nightly to determine if customers have uploaded Excel files into this location. The SSIS job will then go through each of the Excel files found and do ETL on them & create new individual Excel files with the original data transformed into a consistent layout preferred by my client so all of the files end up looking the same aside from of course different data in each file. Most of the Excel files coming in from customers are going to be from fairly recent version of Excel, so I've installed the Microsoft Access Database Engine 2010 Redistributable on my development environment's virtual machine (the 32-bit version... AccessDatabaseEngine.exe).
Others here know far more about this than I do, but here is a suggestion:
Make sure the packages using Excel sources/destinations are configured to run AS 32-bit packages. If you're using Excel as a source or destination then you have to do this; I've never seen a workaround for it. I think there are a couple of others that require a 32-bit environment too, but most don't.
If I'm wrong I am 100% positive someone here will pick it up. 😀
I'll pick it up.
There is a 64-bit ACE OLE DB provider, so you can run a package with Excel source/destination perfectly on a 64-bit machine.
Really? Sweet! See, you learn something new every day. Hopefully that's not the only thing I learn today as it's not even 9am yet. 😀
May 21, 2014 at 10:53 am
SQL is delicious (5/21/2014)
Koen Verbeeck (5/20/2014)
SQL is delicious (5/1/2014)
brad.mccollum (5/1/2014)
To give you a little more insight as to the general nature of the SSIS package, it will basically be looping through a folder once nightly to determine if customers have uploaded Excel files into this location. The SSIS job will then go through each of the Excel files found and do ETL on them & create new individual Excel files with the original data transformed into a consistent layout preferred by my client so all of the files end up looking the same aside from of course different data in each file. Most of the Excel files coming in from customers are going to be from fairly recent version of Excel, so I've installed the Microsoft Access Database Engine 2010 Redistributable on my development environment's virtual machine (the 32-bit version... AccessDatabaseEngine.exe).
Others here know far more about this than I do, but here is a suggestion:
Make sure the packages using Excel sources/destinations are configured to run AS 32-bit packages. If you're using Excel as a source or destination then you have to do this; I've never seen a workaround for it. I think there are a couple of others that require a 32-bit environment too, but most don't.
If I'm wrong I am 100% positive someone here will pick it up. 😀
I'll pick it up.
There is a 64-bit ACE OLE DB provider, so you can run a package with Excel source/destination perfectly on a 64-bit machine.
Really? Sweet! See, you learn something new every day. Hopefully that's not the only thing I learn today as it's not even 9am yet. 😀
You can download it here:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
This blog post explains how you can install it on a machine where the 32-bit provider also has been installed:
Force installation of 64-bit ACE OLE DB provider[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply