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

0
votes

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;

1
sqlssisodbc
Is this by any chance a 64-bit machine? Some of the data drivers are not installed on 64 bit servers by default. – Jeremy Holovacs
No, it's a 32-bit server – Brandon
What ODBC connection exists on your dev box that it can't find on QA? What is the Package Protection level set at? – billinkc
I looked in my "Data Source Administrator" (I'm not sure if that's the correct place or not) and the server has more drivers than my dev machine. It's missing "SQL Server Native Client 10.0", but it has the other SQL drivers that I have. ProtectionLevel is "DontSaveSensitive" – Brandon
Yes, they are both on the same domain and the database server is pingable from the test server. – Brandon

1 Answers

3
votes

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!!!