Link to home
Start Free TrialLog in
Avatar of Gerhardpet
GerhardpetFlag for Canada

asked on

Problems with SSIS

We are using SQL Server Integration Services on several servers for our customers. We sync data from a Pervasive database and calling the SSIS packages from a .NET application. All servers are working fine using the .Net Providers\Odbc Data Provider but now we have one server where we can't get it to work.

We get this error
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

When I do a manual import from Pervasive using the .Net Providers\Odbc Data Provider it works fine

The different on this server from the other ones is:
Windows 2008 Enterprise SP2 (all other server are Windows 2008 R2 Standard)
MS SQL 2008 (all other servers are MS SQL 2008 R2)


All servers are 64 bit.

Any idea what to look for?
Avatar of lcohan
lcohan
Flag of Canada image

<<We get this error
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified >>

You must look for that Odbc Data Provider on that .." one server where we can't get it to work. "

Its under Control Panel - Admin Tools - ODBC Data sources and make sure that the Pervasive user running the .NET application can access it.
Avatar of Gerhardpet

ASKER

You must look for that Odbc Data Provider on that .." one server where we can't get it to work. "

Its under Control Panel - Admin Tools - ODBC Data sources and make sure that the Pervasive user running the .NET application can access it.

Been over this a hundred times already. It is not a permission issue.

As I said when I do a manual import from Pervasive using the .Net Providers\Odbc Data Provider it works fine
Hi,

This isn't a 32bit vs 64bit issue is it?

That is, are all your versions of windows/sql/etc 32bit?

Regards
  David
Correct it is not related to 32bit vs 64bit.

 We have the same .NET application working on 5 other servers of which 4 are 64 bit and 1 is a 32bit.

Pervasive 10 only supports 32 bit so we connect to the 32bit ODBC data source.

That is not the issue here.
Hi,

The issue isn't if Pervasive is 32 or 64bit, the issue is if you can find the correct bit driver for your application.

If your application is 64bit, then it isn't going to accept a 32bit driver - generally speaking.

Given your servers are 64bit, are you using the correct version of odbc administrator? There are two versions, and they look identical, and you can only run one at a time if memory is correct ...

Regards
  David
Thanks for trying to help David but did you read my original question?

I know all about the issue of selecting the right driver for Pervasive (32bit vs 64bit) and that is not the problem here.

The different on this server from the other servers is:
Windows 2008 Enterprise SP2 (all other server are Windows 2008 R2 Standard)
MS SQL 2008 (all other servers are MS SQL 2008 R2).

Again to manually run an import job it works fine (which uses ISSIS as well) but to run it from a package it does not work where we get this error

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Hi,

Have you created a user DSN vs a System DSN?

Regards
  David
ASKER CERTIFIED SOLUTION
Avatar of Gerhardpet
Gerhardpet
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Found the solution myself