0

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.

William Rose
  • 971
  • 1
  • 6
  • 13

1 Answers1

0

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.)

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • This sounds very promising. I had been thinking I might need to do something horrible like create views in SQL server that were a SELECT * FROM OPENROWSET(...), so writing SELECT * FROM "double"."quoted" is much more preferable. – William Rose Apr 26 '10 at 10:03
  • Thwarted by SSIS, again! It wraps the hand-written SQL with a query that uses bracket quotes. I edited the question to explain further. – William Rose Apr 27 '10 at 22:29
  • @William Rose - I can't test this because I don't currently have acces to an SSIS installation, but is there an option in the Ingres connection configuration to enable ANSI quoted identifiers? – Ed Harper Apr 28 '10 at 06:59
  • I've written this three times now and it keeps getting lost: third time lucky? ANSI SQL-92 says quoted identifiers use double quotes, not brackets, so the Ingres driver supports ANSI quoted identifiers only (there's no option to turn them on or off). I checked the ODBC driver code to ensure it was returning the double quote character when interrogated about the supported SQL features, and it appears it does. So I don't think it's something the driver is doing wrong. – William Rose Apr 28 '10 at 10:24
  • @William Rose - apologies, I don't think I was clear - I meant the advanced properties of the connection manager inside SSIS. – Ed Harper Apr 29 '10 at 08:10
  • @William Rose - the other possibility would be to do without the DSV – Ed Harper Apr 29 '10 at 08:11
  • @Ed Harper - I can't find any more advanced properties of the connection manager, the data source or the data source view that refer to ANSI quoted identifiers. I think I will have to make do without the DSV! – William Rose Apr 29 '10 at 22:08