?
Solved

SSIS Jobs for SQL2005 32bit usable on SQL2008 server 64bit?

Posted on 2010-08-18
7
Medium Priority
?
487 Views
Last Modified: 2013-11-10
Hi Experts,

we are currently running an sql server2005 32bit with several SSIS jobs.
Now we would like to move the database and all SSIS jobs to another server.
This new server has a 64bit SQL server 2008 running.

Will we experience any problems? Any certain steps we have to do?
Or is it just importing the jobs and let them run?
Thanks for your answers!

0
Comment
Question by:arthrex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 30

Accepted Solution

by:
Reza Rad earned 2000 total points
ID: 33462410
0
 
LVL 3

Expert Comment

by:PrakashRaoBS
ID: 33462454
while migrating SSIS packages to sql server 2008 64bit, package will excute when you try from sql server integration services. If you want to run SSIS packages from SQL Server Jobs then it won't work.

workaround -

Create the job in operating system(execmd) instead of SSIS.
execute SSIS package using DTExec utility from this path C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
0
 

Author Comment

by:arthrex
ID: 33462608
thanks all for your answers.

I'm either executing the SSIS Jobs via SQL Server Agent or during development directly in BI Development Studio.
@PrakashRaoBS you wrote that the job won't work from sql server jobs (you mean sql server agent)?
But in this article reza_rad posted
http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html
they say, that there is a property to select the bitness in SQL Server Agent. And it sounds, as if it should work.

What exactly is not working when running SSIS packages from SQL Server AGent?
Is this a general problem or is it just not possible to run an SSIS Job in 64 bit mode when a 32 bit driver is used in the Job. e.g. for Excel connection?

Thank you very much for your help
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 30

Expert Comment

by:Reza Rad
ID: 33462717
you can use 32bit version of DTEXEC and everything can be fine:

Hurdle #1 - Finding the 32-bit DTExecFinding the executable shouldn't be difficult.  In a standard 64-bit installation, the 32-bit DTExec.EXE should be located in the "\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn" folder.  It's plainly called "DTExec.EXE", and doesn't identify itself in any way as a 32-bit app - you'll have to "know" it is by it being located in the 32-bit folder.  (Or you could try to execute it and watch Task Manager.)  If you've installed SQL to a non-standard location, you may have to hunt a little.  If you can't find it at all, you may not have installed the 32-bit components on your 64-bit machine.  During the install of SQL Server, if you only selected "Integration Services" and didn't install "Business Intelligence Development Studio" OR "Management Tools - Complete", then you won't have the 32-bit DTExec installed.  You'll have to run SQL Setup, and install one of those options.
from Todd blog again.

I strongly recommend to read Todd article completely, Todd is one of front runners of SSIS.


0
 
LVL 3

Expert Comment

by:PrakashRaoBS
ID: 33462804
you are right Arthrex. It just not possible to run an SSIS Job in 64 bit mode when a 32 bit driver is used in the Job. Otherwise we can use.
you can also specify the bitness in Sql server agent, but it will work only in sql server 2008. If you want to run it in sql server 2005 64 bit, then you need to execute your SSIS package using the 32 bit DTexec utiltiy.
0
 

Author Closing Comment

by:arthrex
ID: 33473184
Thank you! You helped a lot!
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33474233
your welcome,
Regards,
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question