CODEWITHSUNDEEP
sql-serverssisodbc
Jyothish Bhaskaran
Jyothish Bhaskaran

Reputation: 559

Unable to select ODBC source in SSIS lookup

I am creating a package for updating a table in SQL server. The details to be updated is fetching from the MySQL database. For this I am using ODBC connection manager to connect to MySQL. But this ODBC connection manager is not getting listed in the OLE DB connection manager. I can see ODBC Source and destination in Toolbox. Similarly is there any ODBC lookup for doing this?

Upvotes: 3

Views: 2668

Answers (2)

Jyothish Bhaskaran
Jyothish Bhaskaran

Reputation: 559

I though of answering my own question as I got an easy solution for the same. Hopefully it will become a help to other beginners in SSIS.

Creating a Datasource for MySQL is an easy task. The whole process can be done with the steps mentioned below.

  1. Install the MySQL connector.
  2. Create ODBC DSN.
  3. Create datasource using the created DSN.

Lets discuss each step in detail now.

1)Install the MySQL connector.

MySql connector is readily available for download in the MySQL web site. Download the correct one for your machine and install. It will be better if you install bot the 32 bit and 64 bit versions. When I tried using the 64 bit alone, I was getting an error regarding a platform mismatch.

2)Create ODBC DSN.

Open ODBC data source administrator. If your machine is 32 bit then open 32 bit other wise 64 bit ODBC data source administrator. Mine is 64 bit machine. It can be opened by Control Panel->Administrative Tools->ODBC DataSources. Mine is 64 bit machine. So I am opening the ODBC Data Sources(64 bit)

enter image description here

Go to System DSN tab. Click Add button. Below window will appear. enter image description here

If the connectors are installed properly as per step 1, then they should be listed here. Select the MySQL ODBC 5.3 Unicode Driver. Click Finish. The below window will appear. Enter the credentials and Press Test button. If everything is fine, it should show a success message. Then Click on Ok Button.

enter image description here

Then the created DSN should be listed in the System DSN tab.

enter image description here

Repeat the same for User DSN also. Because in the SSDT while configuring the datasource we wont be able to select the System DSN. Also when deploying we wont be able to work with the User DSN also. Creating both in same name will save the package editing time. This is what happened in my case. I spent a lot of time investigating the reason for the package failure. Finally I got this. Please correct me if I am wrong. 🙂

3)Create datasource using the created DSN.

Add a connection manager in the SSDT by right clicking the connection managers in solution explorer and selecting New Connection Manager. Select ODBC from the options available.

enter image description here

Click Add. In the window that appears click New. A new window will appear.

enter image description here

In the data source specification select the Use user or System Data Source Name. Select the created DSN from the drop down box and press Test Connection. It should show success message.

That’s it. We created the ODBC source.

Please refer my blog for the article

Upvotes: 0

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

Lookup components only support the use of OLEDB connection managers or Cache connection managers. For your purposes, you should use the latter:

  1. Read the MySql table using ODBC
  2. Use the cache transform to load a .caw file.
  3. Configure the lookup to read from the cache file.

Upvotes: 5

Related Questions