Postgresql destination data truncation issue in SSIS

Hi All,

My Source is SQL and Destination is Postgresql.
I have column called Personal_ID as Varchar() datatype and in Destination i have Text(Unicode text stream) data type.

so I have used data convertion to convert the data type DT_WSTR to DT_NTEXT.

but SSIS package is running fine but data not inserting properly.
I have values like "123456" in the source but in destination am getting only "1(first character)"
Its happenning only when am moving the data to PostgreSQL.**

Note:Am using intellisoft oledb provider to connect the postgresql.

Thanks in advance

3 answers

Monalv-MSFT 5,896 Reputation points

2021-02-26T08:17:19.057+00:00

Hi @pandiyanrajendran rajendran ,

Please check the data type of column in Postgresql Destination.

If the length is short in destination, please add length in the data type.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
pandiyanrajendran rajendran 1 Reputation point

2021-02-26T09:42:39.437+00:00

Hi MonalV,

The destination data type is Text which means, it will allows us to insert maximum of length.

Thanks,
Pandiyan R

Monalv-MSFT 5,896 Reputation points

2021-03-07T09:14:02.59+00:00

Hi @pandiyanrajendran rajendran ,

May I know if you use other ssis transformations?

Best regards,

Mona
Sign in to comment
Tom Phillips 17,716 Reputation points

2021-02-26T14:43:06.14+00:00

What exactly is in your data conversion task?
Please sign in to rate this answer.
pandiyanrajendran rajendran 1 Reputation point

2021-02-26T15:03:23.623+00:00

Hi Tom,

I have nvarchar(100) in source for employeed id and in destination I have text data type which will allow us to insert max of length.

so am using data conversion to convert Nvarchar(DT_WSTR) to TEXT(DT_NTEXT) to map the destination column. now I am not getting any issue but after completing the load am checking the destination table, by the in the employee id am ihaving the "1" instead of "123456".

Erland Sommarskog 102K Reputation points MVP

2021-02-26T22:03:22.083+00:00

Did you pay attention to the word "exactly" in Tom's question?

If we don't see the code you are using, how do you expect us to be able to tell us what is wrong?

Having the definition for the Postgres table helps too.

pandiyanrajendran rajendran 1 Reputation point

2021-02-27T03:15:26.667+00:00

Hi Erland,

please find what exactly in data conversion and table defination

table defination:

CREATE TABLE person (
guid uuid NOT NULL
,tenant_guid uuid NOT NULL
,first_name TEXT COLLATE pg_catalog."default" NOT NULL
,middle_name TEXT COLLATE pg_catalog."default"
,last_name TEXT COLLATE pg_catalog."default" NOT NULL
,prefix TEXT COLLATE pg_catalog."default"
,suffix TEXT COLLATE pg_catalog."default"
,active boolean NOT NULL DEFAULT true
,create_user uuid
,create_date TIMESTAMP without TIME zone NOT NULL
,update_user uuid
,update_date TIMESTAMP without TIME zone NOT NULL
,hib_version BIGINT NOT NULL
,contact_guid uuid
,user_account_guid uuid
,deleted boolean DEFAULT false
,employee_id TEXT COLLATE pg_catalog."default"
,preferred_name TEXT COLLATE pg_catalog."default"

) TABLESPACE pg_default;
Sign in to comment
sreekanth mohan 1 Reputation point

2021-10-06T19:11:15.223+00:00

NVARHAR(DT_WSTR) can be mapped to TEXT(DT_NTEXT) in the OLEDB Destination but you need to make "Validate External Meta Data" property of the destination to "False".
I know Pandiyan got this work around. This might be helpful for some one so posting it here.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment