Any type not supported for column

Question

I'm building a pipeline to do an upsert from a SQL Server table to a MySQL table.

I'm using a Data flow and Alter row settings to map the columns. When I run in debug, I get the error "Any type not supported for column UUID". The column in MySQL for UUID is:

uuid(UNI, binary, NULL, nullable)

The MySQL dataset connects fine, but when I try to test connection from the data flow I get the error. I'd like to bypass (ignore) the UUID column, but I'm not sure how. Any suggestions?

Answer 1

Thanks for your response.

The MySQL table:

CREATE TABLE patient_data (
id bigint(20) NOT NULL,
uuid binary(16) DEFAULT NULL,
title varchar(255) NOT NULL DEFAULT '',
language varchar(255) NOT NULL DEFAULT '',
financial varchar(255) NOT NULL DEFAULT '',
fname varchar(255) NOT NULL DEFAULT '',
lname varchar(255) NOT NULL DEFAULT '',
mname varchar(255) NOT NULL DEFAULT '',
DOB date DEFAULT NULL,
street varchar(255) NOT NULL DEFAULT '',
postal_code varchar(255) NOT NULL DEFAULT '',
city varchar(255) NOT NULL DEFAULT '',
state varchar(255) NOT NULL DEFAULT '',
country_code varchar(255) NOT NULL DEFAULT '',
drivers_license varchar(255) NOT NULL DEFAULT '',
ss varchar(255) NOT NULL DEFAULT '',
occupation longtext DEFAULT NULL,
phone_home varchar(255) NOT NULL DEFAULT '',
phone_biz varchar(255) NOT NULL DEFAULT '',
phone_contact varchar(255) NOT NULL DEFAULT '',
phone_cell varchar(255) NOT NULL DEFAULT '',
pharmacy_id int(11) NOT NULL DEFAULT 0,
status varchar(255) NOT NULL DEFAULT '',
contact_relationship varchar(255) NOT NULL DEFAULT '',
date datetime DEFAULT NULL,
sex varchar(255) NOT NULL DEFAULT '',
referrer varchar(255) NOT NULL DEFAULT '',
referrerID varchar(255) NOT NULL DEFAULT '',
providerID int(11) DEFAULT NULL,
ref_providerID int(11) DEFAULT NULL,
email varchar(255) NOT NULL DEFAULT '',
email_direct varchar(255) NOT NULL DEFAULT '',
ethnoracial varchar(255) NOT NULL DEFAULT '',
race varchar(255) NOT NULL DEFAULT '',
ethnicity varchar(255) NOT NULL DEFAULT '',
religion varchar(40) NOT NULL DEFAULT '',
interpretter varchar(255) NOT NULL DEFAULT '',
migrantseasonal varchar(255) NOT NULL DEFAULT '',
family_size varchar(255) NOT NULL DEFAULT '',
monthly_income varchar(255) NOT NULL DEFAULT '',
billing_note text DEFAULT NULL,
homeless varchar(255) NOT NULL DEFAULT '',
financial_review datetime DEFAULT NULL,
pubpid varchar(255) NOT NULL DEFAULT '',
pid bigint(20) NOT NULL DEFAULT 0,
genericname1 varchar(255) NOT NULL DEFAULT '',
genericval1 varchar(255) NOT NULL DEFAULT '',
genericname2 varchar(255) NOT NULL DEFAULT '',
genericval2 varchar(255) NOT NULL DEFAULT '',
hipaa_mail varchar(3) NOT NULL DEFAULT '',
hipaa_voice varchar(3) NOT NULL DEFAULT '',
hipaa_notice varchar(3) NOT NULL DEFAULT '',
hipaa_message varchar(20) NOT NULL DEFAULT '',
hipaa_allowsms varchar(3) NOT NULL DEFAULT 'NO',
hipaa_allowemail varchar(3) NOT NULL DEFAULT 'NO',
squad varchar(32) NOT NULL DEFAULT '',
fitness int(11) NOT NULL DEFAULT 0,
referral_source varchar(30) NOT NULL DEFAULT '',
usertext1 varchar(255) NOT NULL DEFAULT '',
usertext2 varchar(255) NOT NULL DEFAULT '',
usertext3 varchar(255) NOT NULL DEFAULT '',
usertext4 varchar(255) NOT NULL DEFAULT '',
usertext5 varchar(255) NOT NULL DEFAULT '',
usertext6 varchar(255) NOT NULL DEFAULT '',
usertext7 varchar(255) NOT NULL DEFAULT '',
usertext8 varchar(255) NOT NULL DEFAULT '',
userlist1 varchar(255) NOT NULL DEFAULT '',
userlist2 varchar(255) NOT NULL DEFAULT '',
userlist3 varchar(255) NOT NULL DEFAULT '',
userlist4 varchar(255) NOT NULL DEFAULT '',
userlist5 varchar(255) NOT NULL DEFAULT '',
userlist6 varchar(255) NOT NULL DEFAULT '',
userlist7 varchar(255) NOT NULL DEFAULT '',
pricelevel varchar(255) NOT NULL DEFAULT 'standard',
regdate date DEFAULT NULL COMMENT 'Registration Date',
contrastart date DEFAULT NULL COMMENT 'Date contraceptives initially used',
completed_ad varchar(3) NOT NULL DEFAULT 'NO',
ad_reviewed date DEFAULT NULL,
vfc varchar(255) NOT NULL DEFAULT '',
mothersname varchar(255) NOT NULL DEFAULT '',
guardiansname text DEFAULT NULL,
allow_imm_reg_use varchar(255) NOT NULL DEFAULT '',
allow_imm_info_share varchar(255) NOT NULL DEFAULT '',
allow_health_info_ex varchar(255) NOT NULL DEFAULT '',
allow_patient_portal varchar(31) NOT NULL DEFAULT '',
deceased_date datetime DEFAULT NULL,
deceased_reason varchar(255) NOT NULL DEFAULT '',
soap_import_status tinyint(4) DEFAULT NULL COMMENT '1-Prescription Press 2-Prescription Import 3-Allergy Press 4-Allergy Import',
cmsportal_login varchar(60) NOT NULL DEFAULT '',
care_team int(11) DEFAULT NULL,
county varchar(40) NOT NULL DEFAULT '',
industry text DEFAULT NULL,
imm_reg_status text DEFAULT NULL,
imm_reg_stat_effdate text DEFAULT NULL,
publicity_code text DEFAULT NULL,
publ_code_eff_date text DEFAULT NULL,
protect_indicator text DEFAULT NULL,
prot_indi_effdate text DEFAULT NULL,
guardianrelationship text DEFAULT NULL,
guardiansex text DEFAULT NULL,
guardianaddress text DEFAULT NULL,
guardiancity text DEFAULT NULL,
guardianstate text DEFAULT NULL,
guardianpostalcode text DEFAULT NULL,
guardiancountry text DEFAULT NULL,
guardianphone text DEFAULT NULL,
guardianworkphone text DEFAULT NULL,
guardianemail text DEFAULT NULL,
enrollment_date date DEFAULT NULL,
facility_id int(11) DEFAULT NULL,
PCP text DEFAULT NULL,
memberID text DEFAULT NULL,
hist_memberID varchar(15) DEFAULT NULL,
ipoc_due_date date DEFAULT NULL,
schedule_follow_up_due_date date DEFAULT NULL,
risk_level varchar(50) DEFAULT NULL,
fall_risk varchar(50) DEFAULT NULL,
end_date text DEFAULT NULL
)

The SQL Server table:

CREATE TABLE [stage].PatientData

Please note that the UUID column is not in the SQL Server table and is not used in the ADF mapping.

Answer 2

Hello @Andrew Dvorak ,

Thanks for the details.

To Bypass the UUID column from the dataflow, please follow the below.

Please unselect "Auto Mapping" on the sink mapping tab and map the columns manually. You can remove the UUID column using the delete button next to the output mapping.

Please see the below screenshot for your reference. Let me know if you have any further questions.

------------------------------

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 3

Thanks for trying to help. Unfortunately, the error isn't due to mapping since I had already followed the steps you outlined. The UUID column isn't mapped, but the sink still fails due to the datatype mismatch in the destination table.