Executing a package in the Integration Services Catalogs
-
Hi,
I 'm running into an error message when trying to execute a package through a sql server agent job. Here are the details:
Originally, this package was created in SSIS 2008, along with about 8 other packages. I have migrated everything over to SSIS 2017. I have been able to successfully run my other packages in Integration Services Catalogs via the sql server agent job. These packages all either have an odbc or .net connection to my SQL Server Management Studio 2016 and a separate Oracle DB (both of these I remote connect into my clients servers to access). The only difference with the package that is failing, is I have a .net connection manager to a 3rd party DB that I retrieve data from. In SSIS 2017, when I first attempted to execute this package, I was getting an error saying:
"The specified DSN contains an architecture mismatch between the driver and application"
When I google this, it says "To resolve this error, use the 32-bit odbcad32.exe to configure or remove the DSN. A data source associates a particular ODBC driver with the data you want to access through that driver."
However, in SSIS, I changed the target server version to SQL Server 2017, and it executes successfully in SSIS.
I have deployed all "9" packages to the Integration Services Catalogs. Every package works, except for this one. Any thoughts of what I need to do to fix this issue?
Thanks in advance for your help. Hope I gave enough info.
-
You can either run the package through the Integration Service Catalog or download the package on the file system and execute it from there. Have you tried testing this package from Visual Studio with the package on the file system using SQL Server 2017?
- askaggs.edt - Monday, July 30, 2018 4:06 PM
You say that the difference between the packages which work and the one which does not is the .Net connection, but then that your Google search suggests that this is an ODBC problem. I don't see how it can be an ODBC issue, as your other packages are functioning OK.
You could try running the package in 32-bit mode from within the SQL Agent job, just to see whether that changes anything.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Tuesday, July 31, 2018 5:54 AMaskaggs.edt - Monday, July 30, 2018 4:06 PM
You say that the difference between the packages which work and the one which does not is the .Net connection, but then that your Google search suggests that this is an ODBC problem. I don't see how it can be an ODBC issue, as your other packages are functioning OK.
You could try running the package in 32-bit mode from within the SQL Agent job, just to see whether that changes anything.
I was originally running the packages from the Integration Services Catalogs. From there, I didn't have the option of changing the 32 bit runtime. I imported the package to the SSIS Package Store, and then I had the option in the Agent job to update to run at 32 bit. This appears to have fixed my issue. I can execute a job from the Agent and it successfully executed. thanks for the help
- askaggs.edt - Tuesday, July 31, 2018 12:36 PM
Can't remember earlier versions, but in SQL Server 2017, the option is available regardless of where the package is stored.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi.
We had a similar problem and we eventually set up two system DSNs. One using the 64bit driver; the second using the 32bit driver. The two have an identical name. Whether I run the package in Visual Studio or through a scheduled task (32 bit or 64 bit) the connection string is the same - "Dsn=ABCDEFG" but the correct driver is used.
Hope that helps!
- Bevan Keighley - Tuesday, July 31, 2018 3:12 PM
I've done this too. Works well, as long as 32- and 64-bit versions of the required driver are available, of course.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply