SSIS DSN Not Showing as ODBC Data Source

Question

I have been following the directions here:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/05ccd778-b78c-4a83-a10a-c4ae412cc6e4

And ran into a problem where my System DSN is not showing up as a ODBC provider.

I found this which seemed promising:

http://support.microsoft.com/kb/2000277

I was not able to delete the key but followed but I did what was suggested:

"If unable to delete the key, double-click the key and erase the Data value entered. Once done, the value should read ' (value not set) '"

However, after following the instructions my System DSN still does not appear as an option.

The USER DSN however does show...has shown but does not work as I get a permissions error.

Any ideas?

Solution

Have you verified you're not using a 32-bit driver on a 64-bit OS?

http://support.microsoft.com/kb/942976

32 = C:\Windows\SysWOW64\odbcad32.exe   64 = C:\Windows\System32\odbcad32.exe

Comments

[user1114330]

Go Tech! (Atlanta Native) I have tried both 32 and 64 separately and also installed alongside each other. I am definitely using the 64 bit driver on a 64 bit machine assuming. However I started testing with the 32 bit when I read that BIDS didnt support the 64 bit drivers and was thinking that may be my issue as well.

[user1114330]

yeah...there is an odbcad32.exe file in each of the folders mentioned. For what I am trying to do (sync a MSSQL view with MySQL) ODBC the best choice? Should I be looking at the .net drive for mysql instead? Also...maybe the problem is that even after uninstalling both drivers the files you mentioned are still there...maybe a conflict?

[user1114330]

Where I am even more confused is that the User DSN shows in the ODBC selection list...however, it won't connect. Gives yet another error even though connect test is successful when setting up the User DSN.

[anon]

@user1114330. If the connection test is working in the DSN settings area, that's a good first step... Make sure your DSN names are unique... I name mine like "system-DSN-32" and "system-DSN-64" to differentiate them. My 32-bit apps need to use the 32-bit DSN and similar for 64-bit apps

[user1114330]

@BrianAdkins...right, at least some progress has been made. Definitely named them uniquely. My problem now is that I tried to create a DSN using the odbcad32.exe that is in the SysWOW64 folder...it first tells me I do not have permissions (my account has admin rights). I read to ignore and try creating the DSN anyway...only problem the MySQL ODBC drivers are not in the list of options?

[user1114330]

Im on a 64 bit machine and have tried to install and use both the 32 bit and 64 bit connectors. Right now I only have the 64 bit installed but both odbcad32.exe files exist in both the syswow64 and the system32 folders. The latter I am assuming is left over from the 32 bit installation.

[anon]

Start with the application that will be accessing the data via odbc... If it is 32bit, then you need a 32bit MySQL connector and will need to use the 32bit version of the windows odbc admin. If you have no 64bit apps needing the data, then you have no need for 64bit versions of any of that (they will only cause trouble)

[user1114330]

Im running SQL server 2012 64 Bit. Visual Studio 2010...they were installed during the same installation.

[user1114330]

Do you think this "ODBC System DSN Warning" message that pops up when I open up the 64 bit version of the ODBC Data Source Administrator (odbcad32.exe) has something to do with this? I get that its just a warning and others have been successful creating a DSN however I can't get that far because the MySQL ODBC driver does not show up in the list of drivers available.