Не отображается список столбцов в SSIS

[Indever2]

Использую Visual Studio 2019 и Integration Services Project v3.0, версия драйвера: Release v1.0.0.20190409

Проблема возникает при использовании любого драйвера (32/64 bit, ANSI/UTF), а также старых версий. Создание диспетчера подключений с помощью DSN, а также подключение к БД проходит успешно, можно даже просмотреть данные в таблице:

Однако, если перейти во вкладку выбора столбцов, то можно наблюдать следующую картину:

Подключение успешно, но почему-то диспетчер подключений не может получить список столбцов. Возможно ли, что эта проблема на стороне Visual Studio, и можно как-то обойти ее и-таки получить доступ к столбцам таблицы в ClickHouse?

Заранее спасибо за помощь.

[Indever2]

Скачал драйвер с опцией debug, в лог-файле (C:\Temp\clickhouse-odbc) после открытия окна, показанного на последнем скриншоте вижу следующие строки (для другой таблицы):

_C:\dev\clickhouse-odbc\driver\odbc.cpp:89 query(85) = [select "sp_id" ,"datetime" ,"pr_id" ,"price" ,"stock_count" ,"usebefore" from prices] C:\dev\clickhouse-odbc\driver\odbc.cpp:136 SQLNumResultCols C:\dev\clickhouse-odbc\driver\odbc.cpp:140 0

[proller]

А можете приложить полный лог файл с минимальным воспроизведением проблемы? (удалить лог, открыть студию и добраться до "столбцы")

[Indever2]

А можете приложить полный лог файл с минимальным воспроизведением проблемы? (удалить лог, открыть студию и добраться до "столбцы") clickhouse-odbc.log

[Indever2]

Выбрал таблицу "prices" со столбцами "sp_id" ,"datetime" ,"pr_id" ,"price" ,"stock_count" ,"use_before".

[proller]

Со стороны драйвера проблемы не видно. Вызывался SQLPrepare но не вызывался SQLExecute

[Indever2]

Со стороны драйвера проблемы не видно. Вызывался SQLPrepare но не вызывался SQLExecute

Ок, понял. Попробую с другими ODBC-драйверами, если там будет такая же проблема - буду писать в техподдержку Microsoft.

[Indever2]

Драйвер ODBC для Postgre отрабатывает корректно: Если нужно, могу сделать аналогичные действия по подключению к Postgres и ClickHouse и захватить сетевой обмен Wireshark'ом, а дамп выложить сюда.

[ratp0is0n]

Наблюдаю, аналогичную ситуацию при подключении ODBC-драйвера к Progress Report Server - запрос выполняется и возвращается его структура, но в источнике данных отсутствует список столбцов.

[hooman72]

Hi All

I am working in one of the biggest companies in the middle east and we are using clickhouse. I want to build my ETL's with SSIS but I can't use clickhouse ODBC as a data-flow source. I even get a preview of data but [data flow\odbc source] is unable to detect columns. we used it in Powerbi without any issues

visual studio 2019 ssis: 3.1 clickhouse ODBC: 1.00.00.5435 clickhouse : 19.8.3.8

Expected behavior it would be great if I can read data from clickhouse and insert it to an excel file using SSIS.

SSIS incompatible with Clickhouse ODBC

[hooman72]

is anyone working on this issue? It is creating a lot of headache for our team.

[Indever2]

is anyone working on this issue? It is creating a lot of headache for our team.

I have the same issue as you. ODBC is not the only way to get data from clickhouse. You could use a HTTP-interface inside of Script Component as a data source.

[filimonov]

Can you please check if the problem is still actual with latest release https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.0.20191028 ?

[Indever2]

Can you please check if the problem is still actual with latest release ... ?

Just checked, still not working. Now there is one more bug: can't select a table via the column selector (infinity "Loading..."):

Direct SQL queries are working, but still no columns:

[filimonov]

Thanks, we'll look on that closer.

[hooman72]

Hi Unlike @Indever2 I still can see tables list but still, there is no column detected.

Thanks

[Ramaseb]

Hi , any news on this ?

[traceon]

Issues mentioned here, with a good chance, are fixed by https://github.com/ClickHouse/clickhouse-odbc/pull/211 once it is merged.

[hooman72]

I have downloaded the new driver from https://github.com/ClickHouse/clickhouse-odbc/releases before this version I could preview the table but right now it doesn't work anymore.( and I can't use data-flow yet) my connection is fine because

1. I can see the list of tables in SSIS
2. odbc works fine in powerbi and I can generate reports.

I think this release is before the #211 merge. can we please get a new release?

this is the error:

===================================

There was an error displaying the preview. (Microsoft Visual Studio)

===================================

ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (CLICKHOUSEODBC.DLL)

---

Program Location:

at System.Data.Odbc.OdbcCommand.Cancel() at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, Int32 noParams, QueryParameter[] parameters, DbConnection connection, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns, Int32 timeout) at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, QueryParameter[] parameters, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns, Int32 timeout) at Microsoft.DataTransformationServices.DataFlowUI.ODBCConnectionPage.previewButton_Click(Object sender, EventArgs e)

[traceon]

I think this release is before the #211 merge. can we please get a new release?

Do you mean, you verified that this particular problem is resolved for you in a custom build that has #211 in it?

[hooman72]

I think this release is before the #211 merge. can we please get a new release?

Do you mean, you verified that this particular problem is resolved for you in a custom build that has #211 in it?

Hi No I have not created a custom build and i am using the build in this link : https://github.com/ClickHouse/clickhouse-odbc/releases

Because I want to use this driver in a production environment I can't trust a custom build. I was just hoping when we get a new release it's fixed.

Thanks

[traceon]

I see, ok, I'll try to reproduce and fix this your case too, and incorporate the fix in the next release.

[filimonov]

We've published new release today. Please give it a try.

[hooman72]

hi @filimonov thanks for your efforts.

but unfortunately status is the same as my last comment and even preview is not working anymore. I can see a list of tables so my connection is fine but when I try to run a simple query like select 'test' as name I get the following error:

There was an error displaying the preview. (Microsoft Visual Studio)

===================================

ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (CLICKHOUSEODBC.DLL)

---

Program Location:

at System.Data.Odbc.OdbcCommand.Cancel() at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, Int32 noParams, QueryParameter[] parameters, DbConnection connection, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns, Int32 timeout) at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, QueryParameter[] parameters, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns, Int32 timeout) at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)

[yurifal]

I'm with @hooman72 confirming that the same behaviour is observed in Tableau. The Tableau (Desktop) is probing the driver for the capabilities and reported that many of them are missing, including basic aggregations (MIN/MAX/COUNTD) and date part functions, to name a few.

The previous release of the driver doesn't possess this behaviour, though.

[traceon]

Thanks for the feedback, guys. We'll look into this.

Meanwhile, @yurifal , the capabilities Tableau is probing for are somewhat different from ODBC DM not finding an API function implementation. Do you mean those capability tests would pass for ClickHouse, if the driver exposed some function in ODBC API, that it is not exposing currently? (Note, I mean not SQL features, but ODBC C interface functions.)

[yurifal]

Frankly, i'm a noob regarding any programming language, so i don't understand what you mean, sorry.

This is the message from Tableau Desktop when starting to create a datasource using the connection to ClickHouse via the ODBC driver version 1.1.2.20191216:

tableau_odbc_probe_error.txt

[traceon]

I see. So this looks like a separate issue to me. I'll look into this, regardless.

[traceon]

So the current state of the investigation is as follows:

ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (CLICKHOUSEODBC.DLL)

The cause of this is found and will be fixed soon.

empty list of columns

This is, as found before, caused by SSIS requesting the column number using a crafted SELECT, where it explicitly lists the columns, then does SQLPrepare() but not SQLExecute(), and gets the resulting 0. The driver is able to retrieve the column info only on executed queries. I'll see if this capability could be communicated properly to the client side, so that the more appropriate method will be chosen. Or this as well could be an issue on the SSIS side.

for some tables, an attempt to retrieve column info when clicking "View Existing Data..." button may result in a dialog with an error, saying Error at Data Flow Task [ODBC Destination [2]]: The number of columns is incorrect.

This is due to SSIS, while being able to retrieve all table info together with corresponding catalog names (databases), still queries table info separately, without specifying the catalog name. As a result, that "View Existing Data..." button will show previews only for tables from the database, that is specified in the DSN (default or other.) At this point, this looks like an error at SSIS side to me.

Regarding Tableau issues - those being addressed separately.

[EvgenyVorobyev]

Очень ждём исправления этого бага)

[traceon]

This is due to SSIS, while being able to retrieve all table info together with corresponding catalog names (databases), still queries table info separately, without specifying the catalog name. As a result, that "View Existing Data..." button will show previews only for tables from the database, that is specified in the DSN (default or other.) At this point, this looks like an error at SSIS side to me.

Regarding this findings, I asked a question here (hopefully, the right place): https://feedback.azure.com/forums/908035-sql-server/suggestions/39305758-ssis-doesn-t-honour-catalogs-of-tables-it-retrieve - please upvote to boost its visibility.

[traceon]

https://github.com/ClickHouse/clickhouse-odbc/pull/225 now contains changes that hopefully address all of the issues (-tableau) mentioned here. However, some of the behavior needed workarounds at the level of the driver, and some will need specific care when using driver with SSIS:

1. Catalog functions, depending on invocation way and matching patterns, may return tables from databases other than the connected database. To access them, e.g., for retrieving column info, client code (in this case SSIS internally) needs to specify the corresponding catalog (i.e., database) for the table. SSIS doesn't do that. As a result, some of the tables that are discovered, cannot be further queried for their column metadata. Driver's behavior is to return only tables for the connected database in trivial catalog function invocation cases. That behavior was extended to cover still not too complicated catalog function invocation that is performed by SSIS. This helps to mitigate SSIS issues when it then queries for columns for that tables. In short, now you will see only the list of tables that belong to your current connected database.

2. Columns successfully extracted now, however, due to some encoding conversion issues, you have to make sure you use Unicode driver, as the SSIS is a Unicode client. It fails to properly handle all the default conversion that Driver Manager does, in case when the client is Unicode and the driver is ANSI. If you use ANSI driver with SSIS you'll see garbage in parts of column names. However, you can verify that the reference test utility that comes with MDAC works well in similar configuration: ANSI ODBCTest vs ANSI driver, ANSI ODBCTest vs Unicode driver, Unicode ODBCTest vs Unicode driver, Unicode ODBCTest vs ANSI driver - all of them retrieve correct columns (meanwhile DM does the same magic of conversion.) And, this should go without saying, you are limited with 32-bit driver when using SSIS.

[hooman72]

this should go without saying, you are limited with the 32-bit driver when using SSIS.

HI SSIS is 64-bit. visual studio is 32-bit. so when you develop it uses the 32-bit driver but when you deploy. it uses the 64-bit driver. I hope by saying this you are referring to the development and we can use 64-bit when we deploy our package.

[traceon]

Yes, I tested only in VS, which is 32-bit. The driver's behavior should be exactly the same, regardless of bitness.

[hooman72]

@traceon when are we going to get a new release version? I can see your changes have been merged but last release version is for 1 month ago,

[traceon]

Today, during the day.

[traceon]

Here you go: https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.3.20200115

[hooman72]

I can confirm this issue is fixed for my use case. Thanks everyone and specially @traceon for your hard works.