Postgres SQL in SSIS Execute SQL Task

• Wolfmeister

  SSCommitted

  Points: 1547

  More actions

  October 25, 2017 at 5:36 am

  #325913

  Has anyone had any experience working with Postgres SQL in SSIS, specifically Amazon Redshift?  I'm able to create an ODBC connection and execute very basic code against a Redshift database - for example, this works - 

  CREATE TABLE IF NOT EXISTS temp_RetargetingEmail_Events
  (
   email     varchar(255),
   event_timedate  timestamp,
   device_timestamp bigint,
   page_type   varchar(55),
   event     varchar(255),
   page_id    varchar(55)
  );

  However this does not (i.e. two separate statements):

  CREATE TABLE IF NOT EXISTS temp_RetargetingEmail_Events
  (
   email     varchar(255),
   event_timedate  timestamp,
   device_timestamp bigint,
   page_type   varchar(55),
   event     varchar(255),
   page_id    varchar(55)
  );

  CREATE TABLE IF NOT EXISTS temp_retargetingemail_frp
  (
   email     varchar(255),
   page_id    varchar(55),
   event_timedate  timestamp,
   device_timestamp bigint
  );

  Executing more than one statement results in an error:

  [Execute SQL Task] Error: Executing the query "CREATE TABLE IF NOT EXISTS temp_RetargetingEmail_E..." failed with the following error: "[DSI] The error message PGOExecuteError could not be found in the en-US locale. Check that C:\Program Files (x86)\Amazon Redshift ODBC Driver\lib\en-US\PGOMessages.xml exists.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  The file it mentions (PGOMessages.xml) does not exist on the server I am running the SSIS package from, is there a fix / workaround for this?  I have a large script with lots of temp tables, and I'd prefer not to have to create a separate task for each temp table that I need to create

  Thanks

• Wolfmeister

  SSCommitted

  Points: 1547

  More actions

  October 25, 2017 at 7:32 am

  #1965125

  Putting GO statements rather than semicolons after each statement seems to work...

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 25, 2017 at 11:20 am

  #1965197

  Wolfmeister - Wednesday, October 25, 2017 7:32 AM

  Putting GO statements rather than semicolons after each statement seems to work...

  And the reason is that a CREATE TABLE must be the first statement in any given batch, and as "GO" is a batch terminator, that's why it's needed.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)