SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question About SSIS 2012 Development in 32-bit Environment, & Upcoming Installation in 64-bit...


Question About SSIS 2012 Development in 32-bit Environment, & Upcoming Installation in 64-bit Environment

Author
Message
brad.mccollum
brad.mccollum
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 334
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.
SQL is delicious
SQL is delicious
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 174
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. :-D
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7253 Visits: 2033
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.

----------------------------------------------------
How to post forum questions to get the best help
brad.mccollum
brad.mccollum
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 334
Turns out u don't have to have Excel installed on the client machine, only the Microsoft Acess 2010 redistributable.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62696 Visits: 13298
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. :-D


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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62696 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62696 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
SQL is delicious
SQL is delicious
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 174
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. :-D


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. :-D
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62696 Visits: 13298
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. :-D


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. :-D


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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search