ODBC Source Task - KISAM ODBC Driver - Unable to retrieve list of tables or run SQL script

Question

I'm attempting to upgrade a package from SSDT_BI 2013 to Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT) with version 15.1.62002.01090. However, I'm encountering an issue where I cannot retrieve the list of tables or execute SQL scripts from an ODBC Source Data Flow Task.

Before the upgrade, I had consistently been able to connect to our legacy 32-bit system using a pre-setup System DSN 32-bit ODBC driver (KISAM 32 bit driver). This allowed me to view a list of tables in the database.

However, in the upgraded environment, when I navigate to the dropdown box labeled 'Name of the table or view', the system momentarily pauses as if it's attempting to fetch the list. But then, it simply displays the message "No tables or views could be loaded." Subsequently, a pop-up box appears with a slightly more detailed error message: "Could not retrieve the table information for the connection manager '<<Connection_Name>>'. Object reference not set to an instance of an object."

This issue is substantial, as it's hindering our ability to create new ODBC extracts.

I've taken the step of verifying the functionality of the DSN using a PowerShell script, and it's confirmed to be working properly. Therefore, it seems that the problem does not lie with the DSN.

Finding a resolution for this issue is crucial, as the current situation is preventing us from effectively using SSDT.

I greatly appreciate your assistance in resolving this matter.

Best regards

Vani

Answer 1

Hi @Vani Priya，

Could you please go to Projects Menu->Package Properties(Last in menu)->Debugging and then change Run64BitRuntime->false for a try.

If this do not work, try to repair Visual Studio.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.