How to change identifier quote character in SSIS for connection to ODBC DSN

Question

I'm trying to create an SSIS 2008 Data Source View that reads from an Ingres database via the ODBC driver for Ingres. I've downloaded the Ingres 10 Community Edition to get the ODBC driver, installed it, set up the data access server and a DSN on the server running SSIS.

If I connect to the SQL Server 2008 Database Engine on the server running SSIS, I can retrieve data from Ingres over the ODBC DSN by running the following command:

SELECT *
FROM OPENROWSET( 'MSDASQL'
               , 'DSN=IngresODBC;UID=testuser;PWD=testpass'
               , 'SELECT * FROM iitables')

So I am quite sure that the ODBC setup is correct.

If I try the same query with SQL Server style bracketed identifier quotes, I get an error, as Ingres doesn't support this syntax.

SELECT *
FROM OPENROWSET( 'MSDASQL'
               , 'DSN=IngresODBC;UID=testuser;PWD=testpass'
               , 'SELECT * FROM [iitables]')

The error is "[Ingres][Ingres 10.0 ODBC Driver][Ingres 10.0]line 1, Unexpected character '['.".

What I am finding is that I get the same error when I try to add tables from Ingres to an SSIS Data Source View. The initial step of selecting the ODBC Provider works fine, and I am shown a list of tables / views to add. I then select any table, and try to add it to the view, and get "ERROR [5000A] [Ingres][Ingres 10.0 ODBC Driver][Ingres 10.0]line 3, Unexpected character '['.".

Following Ed Harper's suggestion of creating a named query also seems to be stymied. If I put into my named query the following text:

SELECT *
FROM "iitables"

I still get an error: "ERROR [5000A] [Ingres][Ingres 10.0 ODBC Driver][Ingres 10.0]line 2, Unexpected character '['".

According to the error, the query text passed by SSIS to ODBC was:

SELECT [iitables].*
FROM 
(
SELECT *
FROM "iitables"
)
 AS [iitables]

It seems that SSIS assumes that bracket quote characters are acceptable, when they aren't. How can I persuade it not to use them? Double quotes are acceptable.

Answer 1

I don't know a way to change the quoted identifier, but you may be able to get around this by creating a blank DSV (click through the DSV wizard without adding any tables) then, rather than adding the tables to the DSV directly, adding them as named queries (right-click the empty DSV and select "New Named Query".

This enables you control the text of the query yourself, and set your own identifiers.

(I'm making this suggestion based on SSIS 2005, but I think 2008 works in a similar way.)