Data source name not found and no default driver specified

Ahhh, another chance to give back to the community. It's bittersweet. It feels good to contribute, but you really go through the fire to figure out something that no one else has.

This one is an error message that I was getting from SQL Server Integration Services (SSIS). I recently inherited a package (isn't that a nice excuse :) and I switched the connection string from Windows integrated to SQL auth. Pretty standard operation, wouldn't you say? Well, I've been around the block enough to not be totally shocked when I got a few error messages:

An error occured on the SSIS Listener Microsoft.SqlServer.Dts.Runtime.Package/Connection manager "JOESPLACE\MSSQLSERVER.TEST" : SSIS Error Code DTS_E_OLEDBERROR.  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".

An error occured on the SSIS Listener Microsoft.SqlServer.Dts.Runtime.TaskHost/Execute SQL Task : Failed to acquire connection "JOESPLACE\MSSQLSERVER.TEMPEST". Connection may not be configured correctly or you may not have the right permissions on this connection.

An error occured while processing the file C:\temp.txt. Details: There was an error executing the SSIS package C:\test.dtsx.  Please check the event log for more information.

Okay, so there's an error in the connection string. Let the troubleshooting begin. The thing about this little guy is that he just wouldn't go away. I tried a bunch of different changes to the connection string; nothing worked.

This package is a little different because we're using a variable to specify the table. I suspected that this had something to do with it, so I tried a bunch of different things. It must have been after I switched to an existing table and then back to the table variable that it started working. I didn't know it at the time, because I was being a little sloppy with my trial-and-error.

Anyway, after I got it to work, I compared the dtsx files before and after. Then, starting with the original version, I made one change at a time until it worked. Turns out that the fix was totally a one-liner. Aaarrggghhh! ...and here it is:

<property id="5852" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[TEMPEST]</property>

In the original file, the entry was blank; that's it.

A couple more bizarre things about this problem:

• The package would run in Visual Studio, but not after being published.
• I deleted the [dbo].[TEMPEST] table and it still worked.

I meant to fix this a while back, but just now got around to it. I think the problem was actually that I was missing the driver in the connection string, just like the error message suggests. Evidently, you can't just use any old .NET connection string; it has to have the driver in it. Ugh.