SSIS ODBC Simba - Error when access table list on ODBC Source \ Destination

0
votes

I'm using Simba ODBC to create a connection with Google Big Query and using SSIS (Visual Studio 2019) to read and write information on Big Query. The connection works fine and when I use the ODBC Source with the query option, I'm able to get data from Big Query and used it inside SSIS. But when I use the list of tables, I get an error as below:

Exception of HRESULT: 0xC0014020 Error in Data Flow Task[ODBC Source [100]]: SQLSTATE: 42000, Message: [Simba][BigQuery] (70) Invalid query: Invalid dataset ID ""TEST"". Dataset IDs must be alphanumeric (plus underscores and dashes) and must be at most 1024 characters long.

I believe that this happens because the list of tables appear between ("), instead of (`).

Print of table list

The same happens when I use the ODBC Destination. Is there a way to change the format in which the table list appears ? Obs.: On the Visual Studio 2015 this table list comes with (`) and I can connect with big query just fine.

1
ssisgoogle-bigqueryodbc

1 Answers

0
votes

I can see that the tool is sending "TEST" as the dataset, however, depending on if Visual Studio is using StandardSQL or LegacySQL, the dataset should be specified as:

# LegacySQL
FROM [myproject:TEST.TABLE_TEST]

# StandardSQL
FROM `myproject:TEST.TABLE_TEST`

I was wondering if Visual Studio accepts a custom query or can be parameterized to remove the quotes. If this doesn't help, could you please share the query that cause the error? I understand that there is a query option (I'm not familiar with Visual Studio) and it is not clear for me the exact moment when the tool returns the error, screenshot without sensitive information would be appreciated.

UPDATE:

You can review the following checkpoints that could help to verify that the Simba driver is correctly set up and it is not the cause of the reported error:

Installation. Check that you are using the last version of the driver. The last version usually contains improvements on the driver.

ODBC Configuration. For example, the Step 13 of the link you will be able to see a drop-down list with the datasets available and select one as the default. If you don't have issues is this step, then the issue could be in the tool that uses the ODBC connection.

Language Dialect. In here you case change between StandardSQL or LegacySQL as needed, for example, you can force your tool to use LegacySQL and use the characters [ and ] that I explained above.

Connection String. If your tool allows to use a string with the connection, you might want to use it and explicitly indicating the default Dataset (among other driver options).