CODEWITHSUNDEEP
sqlssisodbc
Brandon
Brandon

Reputation: 4593

Problem running SSIS package: "Data source name not found and no default driver specified"

I've developed a SSIS package that takes some data as an XML file, processes it and inserts it into our DB. When the package is being debugged (i.e. running in BIDS), it works fine from my local machine. I can also compile the package to a .dtsx file and run it via command line by executing DTExec.exe. I am running SQL Server 2005. When I move it to a test server, also running SQL Server 2005, and Integration services, I cannot execute the package via command line. I keep receiving this error

Error: 2011-08-16 17:50:39.11 Code: 0xC0202009 Source: ProcessSubmissionData Connection manager "Master" Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". End Error

I've searched the web and tried every solution I can find, to no avail. Is there anyone who can help with this? If it helps, this is the connection string that the connection manager is using

Data Source=xxxx;User ID=xxxx;Password=xxxx;Initial Catalog=database;Provider=SQLOLEDB;

Upvotes: 0

Views: 10385

Answers (1)

Garry M. Biggs
Garry M. Biggs

Reputation: 230

The error is telling you that the Data Source Name (DSN) does not exist rather than the ODBC Driver.

So, if you machine is 32bit then it seems you have simply not created an ODBC Data Source (DSN) via the ODBC Administrator.

(The parameters within the DSN itself determine which ODBC Driver is used...)

Also - this needs to be a "System" DSN (Not a User DSN) since applications running as a system service will not have access to User DSNs. User DSNs are only available on a per user basis when that user is logged in!!!

Upvotes: 3

Related Questions