Executing a package in the Integration Services Catalogs

Question

Executing a package in the Integration Services Catalogs

askaggs.edt

SSC Enthusiast

Points: 199
More actions
July 30, 2018 at 4:06 pm

#372602

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 1

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?

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

askaggs.edt - Monday, July 30, 2018 4:06 PM
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 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

askaggs.edt SSC Enthusiast Points: 199 More actions · Answer 3

Phil Parkin - Tuesday, July 31, 2018 5:54 AM
askaggs.edt - Monday, July 30, 2018 4:06 PM
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 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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

askaggs.edt - Tuesday, July 31, 2018 12:36 PM
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.

Can't remember earlier versions, but in SQL Server 2017, the option is available regardless of where the package is stored.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 5

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!

Phil Parkin SSC Guru Points: 245813 More actions · Answer 6

Bevan Keighley - Tuesday, July 31, 2018 3:12 PM
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!

I've done this too. Works well, as long as 32- and 64-bit versions of the required driver are available, of course.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.