Running 32 bit SSIS package
-
This is my first post in this forum, so hello everyone!
Would be most grateful for help with the following issue:
a simple SSIS package (created in Visual Studio 2019) imports a table from an ODBC Source (MySQL Server 5.xxx) into a database in a SQL Server 2022. ODBC Source is 32-bit, the package runs manually in "32-bit Runtime" as it should.
The problem occurs when it is executed through the SQL Server-Agent (also in "32-bit Runtime") with the following error message: there was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.
I get the same error when I run the packege manually, but not in "32-bit Runtime".
I've searched across the forum and found some relevant topics, but for the earlier versions of the SQL Server.
-
I have not used 2022 SSIS. But worked on lower versions, build the package and set it to 64bit runtime under properties in VS. After the package is deployed you can change it to run in 32 bit runtime in SQL Agent Job.
=======================================================================
-
Hi Emperor100,
thanks for the reply!
The thing is that I have already done it, and it's still not working. When I run the packege manualy in 32 bit runtme it works. As soon as I pack it into a job (also with 32b bit runtime on) it stops working 🙁
-
and are you sure the server has the 32 bit installed and not just the 64 bit (preferable most of the times)?
-
You mean the 32 bit drivers for the ODBC connection? Yes, they are installed and the package runs in manual mode -> when I start the deployed package from the SQL Server and checked the box "use 32 bit runtime".
I'm now thinking maybe it has to do somethin with credentials? As the step to import the data is run by the SQL Server-Agent?
-
and how are you running it manually on the server? through command line (32 or 64 bit depending on which folder you execute from) or through the DTUI (32 bit).
and yes it can be permissions if using windows authentication - if username/password as long as you have these stored on a configuration (ssis catalog environments or dtsconfig file) and those are defined correctly within the package, it would work fine.
-
Hi frederico_fonseca,
I'm running the deployed package through SSMS UI and it works fine.
In the configuration of the Agent Windows-Authentification is indicated, the user I'm using to create the Agent job has no permission to access the data on the server I'm connecting to. But the credentials in the SSIS packages themselves are different, user I'm using has access to the target server. Sorry that it's all in German on the screen ...
-
see https://www.sqlservercentral.com/forums/topic/cant-run-package-via-sql-agent#post-3999259 for an image of how to set the execution to 32 bit.
but I have to query WHY you are using a 32 bit driver on a 64 bit server - there are very few reasons for that to be required.
can you give us an image of the error you get? as well as how is your access to remote server set (username/password I think from what you said - which would likely require you to use environments/configurations)
-
The box with the "32-bit runtime" is checked, have done it as I've created the jobs, unfortunately does not help 🙁
I need to import the data from an old MySQL Server, therefore the 32 bit driver.
The scree of the error
Yes, the access to the target server is set through username / password
-
are you using a ODBC connection setup on odbc32 ? if so ensure it is a SYSTEM connection, not a user connection.
ideally a DSN-less connection should be used if possible with that driver.
-
I've changed the ODBC connection to system DNS and it worked. Many thanks for the help!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply