- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- Copy data from SQL to postgres Fast Load
Copy data from SQL to postgres Fast Load
- Lewie
- Topic Author
- Visitor
-
6 years 6 months ago #13862
by Lewie
Copy data from SQL to postgres Fast Load was created 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
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.
6 years 6 months ago #13866
by Moderator
Replied by Moderator on topic Re: Copy data from SQL to postgres Fast Load
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?
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.
- Lewie
- Topic Author
- Visitor
-
6 years 5 months ago #13868
by Lewie
Replied by Lewie on topic Re: Copy data from SQL to postgres Fast Load
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;
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.
- Lewie
- Topic Author
- Visitor
-
6 years 5 months ago #13871
by Lewie
Replied by Lewie on topic Re: Copy data from SQL to postgres Fast Load
Any feedback?
Please Log in or Create an account to join the conversation.
6 years 5 months ago #13872
by Moderator
Replied by Moderator on topic Re: Copy data from SQL to postgres Fast Load
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!
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.
- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- Copy data from SQL to postgres Fast Load
Time to create page: 0.074 seconds
- You are here:
-
Home
-
Forum
-
Main PGNP Forum
-
SQL Server/DTS/SSIS/Linked servers/Replication
- Copy data from SQL to postgres Fast Load