Copy data from SQL to postgres Fast Load

More
2 years 2 days ago #13862 by Lewie
I am trying to speed up the copy of the data from SQL to Postgres in SSIS.
I have read the developer manual and tried to implement the Fast Load feature.
My connection string appears to work (Data Source=edpl;User ID=postgres;Initial Catalog=prod_stuff;Provider=SQLOLEDBPGNP.1;Persist Security Info=True;OPTIMIZER=ON;)
and everything seems to work until I change the SSIS data access mode to "Table or View - Fast Load" in the OLD DB Destination editor.
I end up getting error: -
:Error: invalid byte sequence for encoding "UTF8": 0x00

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

More
2 years 1 day ago #13866 by Moderator
This looks like a very old bug, that was fixed some time ago. Have you tried to perform FastLoad using the latest build of the PGNP OLEDB provider?

If this issue occurs with the latest PGNP build, could you send the source table DDL and some test rows?

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

More
2 years 8 hours ago #13868 by Lewie
If I am using a provider of SQLOLEDBPGNP.1 in the connection string would this not indicate that I am using the newer version or is that not a given?

CREATE TABLE [BSGDelta].[OccupantTypeDelta](
[PropertyID] [int] NOT NULL,
[VariableSegmentID] [int] NULL,
[DeltaActionType] [nvarchar](1) NOT NULL,
CONSTRAINT [PK_OccupantTypeDelta_1] PRIMARY KEY CLUSTERED
(
[PropertyID] ASC,
[DeltaActionType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
PropertyID VariableSegmentID DeltaActionType
1000014 1 U
1000015 1 U
1000016 1 U
1000017 1 U
1000021 1 U
1000022 1 U
1000023 1 U
1000024 2 U
1000025 2 U
1000026 2 U

In Postgres

CREATE TABLE mssql.occupant_type_delta
(
property_id integer,
variable_segment_id integer,
deltaactiontype character varying(2)
)
WITH (
OIDS=FALSE
);
ALTER TABLE mssql.occupant_type_delta
OWNER TO postgres;

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

More
1 year 11 months ago #13871 by Lewie
Any feedback?

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

More
1 year 11 months ago #13872 by Moderator
Hi,
the SQLOLEDBPGNP alias was introduced long time ago. Please check the PGNP provider version and build in the C:\Program Files (x86)\Intellisoft\PGNP\PGNP.dll Properties, in the Detail->FileVersion.

Please update the OLEDB provider to build 3392 or later, and let us know if issue was not resolved.

Thank you!

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

Time to create page: 0.141 seconds
Powered by Kunena Forum