Can't run SSIS package on SQL 2008R2 x64 for link with data source 32 bit by SQL Server agent job
-
Thursday, January 31, 2013 10:35 AM
After I move SSIS to new SQL Server 2008R2 x64 Std. I facing problem when I run SSIS package by using SQL Agent job.
But If I run execute normally package or run on BID, that still work fine.
I run SSIS package name "MSTR_ESGI0002_REFRESG" for connect data source 32 bit(SQL Server 2008R2 32bit) on ERP Server by Link table of MS Access. But I got error as below which I set DSN JSGIDTA of 32 bit already.
If I set DSN of 64bit, that work fine.
c:\windows\sysWOW64\odbcad32.exe
Detail of software on new Server
1. Windows Server 2008 R2 (64-bit) Operating System
2. SQL Server 2008 R2 Standard Edition (64-bit)
3. Microsoft Office 2010 (32-bit)
4. Client Acess ODBC Driver (32-bit)
Could you please help to advice.Thank you very much for your kindness
All Replies
-
Thursday, January 31, 2013 10:46 AM
Set the "Use 32 bit runtime" checkbox on the SQL Server Agent job step. http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx
Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com
- Proposed As Answer by SSISJoostMicrosoft Community Contributor, Moderator Thursday, January 31, 2013 9:12 PM
-
Thursday, January 31, 2013 9:13 PMModerator
Screenshot (from 2012, but nearly the same):
Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
-
Friday, February 01, 2013 9:50 AM
Hi Russ Loski
Thank you for your suggestion
Before I post this problem I already checkbox "Use 32 bit runtime"
Do you have another solution, please advice.
-
Friday, February 01, 2013 9:50 AM
Hi SSISJoost
Thank you for your suggestion
Before I post this problem I already checkbox "Use 32 bit runtime"
Do you have another solution? Please advice.
-
Friday, February 01, 2013 12:28 PMModerator
Is the jobstep on the new server using an other account and are you using integrated security in the connection managers?
Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
-
Monday, February 04, 2013 3:50 AM
Hi SSISJoost
Where is connection manager that you mention? Sorry, I not well in SQL Server
-
Monday, February 04, 2013 6:28 AMModerator
Hi SSISJoost
Where is connection manager that you mention? Sorry, I not well in SQL Server
Open your package(s) in BIDS/Visual Studio and then check the pane right below your Control Flow or Data Flow. All connection Managers are lsted there.
If you are using Configurations to overwrite the default values of the connection managers then you should aso check those.
Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
- Edited by SSISJoostMicrosoft Community Contributor, Moderator Monday, February 04, 2013 6:29 AM
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Friday, February 08, 2013 6:42 AM
.png)
