Description:
What I'm attempting to do: Load data into a MySQL table via ODBC.
What's going wrong (quick summary):
The ODBC driver is complaining that "Specified cast is not valid" even when presented with data that has no conversions to do. This is preventing data being loaded and thus the software being used at all.
Systems used:
Microsoft SSIS 2008R2 for data transfer
Windows XP SP3
MySQL 5.0.91-community-nt-log and 5.5.13-log
ODBC connector (5.1.8)
Detailed explanation:
I'm trying to load issues into an issue tracker (which only runs on MySQL).
My error comes when trying to load the data into the 'issues' table. The driver in SSIS complains that "Specified cast is not valid". This happens even when all data formats are perfectly matched to the specification as seen via the MySQL Workbench.
No data ends up loaded into the system.
The full error messages seen in the SSIS log are:
[Eventum Issues Table [16]] Error: An exception has occurred during data insertion, the message returned from the provider is: Specified cast is not valid.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Eventum Issues Table" (16) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (19). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
I am using the latest version of the ODBC connector (5.1.8) and have tried using both 5.0 and 5.5 MySQL as destinations. All have the same error.
I have searched the bug databases and could not find this reported already.
If you need any more information to solve this, please feel free to ask.
How to repeat:
I have created a minimal test case in SSIS that can demonstrate the problem (along with documentation on each step).
The test database I use is named 'eventum_test', and the code to create the 'issue' table that I am attempting to load data into is as follows (according to MySQL Workbench).
delimiter $$
CREATE TABLE "issue" (
"iss_id" int(11) unsigned NOT NULL auto_increment,
"iss_customer_id" int(11) unsigned default NULL,
"iss_customer_contact_id" int(11) unsigned default NULL,
"iss_customer_contract_id" varchar(50) default NULL,
"iss_usr_id" int(10) unsigned NOT NULL default '0',
"iss_grp_id" int(11) unsigned default NULL,
"iss_prj_id" int(11) unsigned NOT NULL default '0',
"iss_prc_id" int(11) unsigned NOT NULL default '0',
"iss_pre_id" int(10) unsigned NOT NULL default '0',
"iss_pri_id" smallint(3) NOT NULL default '0',
"iss_sta_id" tinyint(1) NOT NULL default '0',
"iss_res_id" int(10) unsigned default NULL,
"iss_duplicated_iss_id" int(11) unsigned default NULL,
"iss_created_date" datetime NOT NULL default '0000-00-00 00:00:00',
"iss_updated_date" datetime default NULL,
"iss_last_response_date" datetime default NULL,
"iss_first_response_date" datetime default NULL,
"iss_closed_date" datetime default NULL,
"iss_last_customer_action_date" datetime default NULL,
"iss_expected_resolution_date" date default NULL,
"iss_summary" varchar(128) NOT NULL default '',
"iss_description" text NOT NULL,
"iss_dev_time" float default NULL,
"iss_developer_est_time" float default NULL,
"iss_impact_analysis" text,
"iss_contact_person_lname" varchar(64) default NULL,
"iss_contact_person_fname" varchar(64) default NULL,
"iss_contact_email" varchar(255) default NULL,
"iss_contact_phone" varchar(32) default NULL,
"iss_contact_timezone" varchar(64) default NULL,
"iss_trigger_reminders" tinyint(1) default '1',
"iss_last_public_action_date" datetime default NULL,
"iss_last_public_action_type" varchar(20) default NULL,
"iss_last_internal_action_date" datetime default NULL,
"iss_last_internal_action_type" varchar(20) default NULL,
"iss_private" tinyint(1) NOT NULL default '0',
"iss_percent_complete" tinyint(3) unsigned default '0',
"iss_root_message_id" varchar(255) default NULL,
PRIMARY KEY ("iss_id"),
KEY "iss_prj_id" ("iss_prj_id"),
KEY "iss_prc_id" ("iss_prc_id"),
KEY "iss_res_id" ("iss_res_id"),
KEY "iss_grp_id" ("iss_grp_id"),
KEY "iss_duplicated_iss_id" ("iss_duplicated_iss_id"),
FULLTEXT KEY "ft_issue" ("iss_summary","iss_description")
)$$
Description: What I'm attempting to do: Load data into a MySQL table via ODBC. What's going wrong (quick summary): The ODBC driver is complaining that "Specified cast is not valid" even when presented with data that has no conversions to do. This is preventing data being loaded and thus the software being used at all. Systems used: Microsoft SSIS 2008R2 for data transfer Windows XP SP3 MySQL 5.0.91-community-nt-log and 5.5.13-log ODBC connector (5.1.8) Detailed explanation: I'm trying to load issues into an issue tracker (which only runs on MySQL). My error comes when trying to load the data into the 'issues' table. The driver in SSIS complains that "Specified cast is not valid". This happens even when all data formats are perfectly matched to the specification as seen via the MySQL Workbench. No data ends up loaded into the system. The full error messages seen in the SSIS log are: [Eventum Issues Table [16]] Error: An exception has occurred during data insertion, the message returned from the provider is: Specified cast is not valid. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Eventum Issues Table" (16) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (19). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. I am using the latest version of the ODBC connector (5.1.8) and have tried using both 5.0 and 5.5 MySQL as destinations. All have the same error. I have searched the bug databases and could not find this reported already. If you need any more information to solve this, please feel free to ask. How to repeat: I have created a minimal test case in SSIS that can demonstrate the problem (along with documentation on each step). The test database I use is named 'eventum_test', and the code to create the 'issue' table that I am attempting to load data into is as follows (according to MySQL Workbench). delimiter $$ CREATE TABLE "issue" ( "iss_id" int(11) unsigned NOT NULL auto_increment, "iss_customer_id" int(11) unsigned default NULL, "iss_customer_contact_id" int(11) unsigned default NULL, "iss_customer_contract_id" varchar(50) default NULL, "iss_usr_id" int(10) unsigned NOT NULL default '0', "iss_grp_id" int(11) unsigned default NULL, "iss_prj_id" int(11) unsigned NOT NULL default '0', "iss_prc_id" int(11) unsigned NOT NULL default '0', "iss_pre_id" int(10) unsigned NOT NULL default '0', "iss_pri_id" smallint(3) NOT NULL default '0', "iss_sta_id" tinyint(1) NOT NULL default '0', "iss_res_id" int(10) unsigned default NULL, "iss_duplicated_iss_id" int(11) unsigned default NULL, "iss_created_date" datetime NOT NULL default '0000-00-00 00:00:00', "iss_updated_date" datetime default NULL, "iss_last_response_date" datetime default NULL, "iss_first_response_date" datetime default NULL, "iss_closed_date" datetime default NULL, "iss_last_customer_action_date" datetime default NULL, "iss_expected_resolution_date" date default NULL, "iss_summary" varchar(128) NOT NULL default '', "iss_description" text NOT NULL, "iss_dev_time" float default NULL, "iss_developer_est_time" float default NULL, "iss_impact_analysis" text, "iss_contact_person_lname" varchar(64) default NULL, "iss_contact_person_fname" varchar(64) default NULL, "iss_contact_email" varchar(255) default NULL, "iss_contact_phone" varchar(32) default NULL, "iss_contact_timezone" varchar(64) default NULL, "iss_trigger_reminders" tinyint(1) default '1', "iss_last_public_action_date" datetime default NULL, "iss_last_public_action_type" varchar(20) default NULL, "iss_last_internal_action_date" datetime default NULL, "iss_last_internal_action_type" varchar(20) default NULL, "iss_private" tinyint(1) NOT NULL default '0', "iss_percent_complete" tinyint(3) unsigned default '0', "iss_root_message_id" varchar(255) default NULL, PRIMARY KEY ("iss_id"), KEY "iss_prj_id" ("iss_prj_id"), KEY "iss_prc_id" ("iss_prc_id"), KEY "iss_res_id" ("iss_res_id"), KEY "iss_grp_id" ("iss_grp_id"), KEY "iss_duplicated_iss_id" ("iss_duplicated_iss_id"), FULLTEXT KEY "ft_issue" ("iss_summary","iss_description") )$$