Quoting and case of field names in SSIS Lookup transform

More
2 years 5 months ago #13822 by theBellHead
I am trying to mimimise the changes required to a set of SSIS packages when moving the target database from MSSQL to Postgres. The ideal outcome would be that SSIS code ran equally well against either target platform.

Because the MSSQL database was built with UPPER_CASE_IDENTIFIERS I have quoted the creation scripts on Postgres to preserve the case of table and field names.

There is a lookup in the flow which executes the following query:
SELECT "INSURED_SURROGATE_ID", "INSURED_REFERENCE", "INSURED_NAME"
FROM "DIM_INSURED"

When executing the query against a PgOleDB datasource I observe the following:
- If the Lower Case Schema advanced configuration option is selected the Preview fails since the field names are forced to lower case
- If the Use Cursor advanced configuration option is selected the Preview returns with lower case field names
- If neither is these options is selected the Preview returns upper case field names as expected
- Even when the Preview returns upper case field names the Columns dialog shows lower case identifiers and the column matching fails

Is this expected behaviour?

Is there a way I can force the driver to return upper case names (as from the lookup query) for use in column matching?

Thanks

Please Log in or Create an account to join the conversation.

More
2 years 5 months ago #13826 by Moderator
Since you need upper case names the "Lower Case Schema" should not be selected. The connection string option was introduced in order to achieve opposite - force all names into lower case for DTSWizard.

What version/build of the product did you use? Could you try with the latest build? The issue seems to be fixed in the latest build.

Please Log in or Create an account to join the conversation.

Time to create page: 0.119 seconds
Powered by Kunena Forum