October 25, 2017 at 5:36 am
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
October 25, 2017 at 7:32 am
Putting GO statements rather than semicolons after each statement seems to work...
October 25, 2017 at 11:20 am
Wolfmeister - Wednesday, October 25, 2017 7:32 AMPutting 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)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply