Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question About SSIS 2012 Development in 32-bit Environment, & Upcoming Installation in 64-bit Environment Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 1:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:26 AM
Points: 35, Visits: 235
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.
Post #1566838
Posted Thursday, May 1, 2014 1:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:44 AM
Points: 48, Visits: 157
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.
Post #1566840
Posted Tuesday, May 20, 2014 5:17 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
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.
Post #1572954
Posted Tuesday, May 20, 2014 6:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:26 AM
Points: 35, Visits: 235
Turns out u don't have to have Excel installed on the client machine, only the Microsoft Acess 2010 redistributable.
Post #1572967
Posted Tuesday, May 20, 2014 11:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1573003
Posted Tuesday, May 20, 2014 11:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1573004
Posted Tuesday, May 20, 2014 11:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1573005
Posted Wednesday, May 21, 2014 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:44 AM
Points: 48, Visits: 157
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.
Post #1573150
Posted Wednesday, May 21, 2014 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1573267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse