Need help in SSIS(SSIS to MySQL ) error while updating date column to MYSQL

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  January 1, 2017 at 10:34 am

  #339084

  hi all,

  Hope all are fine.

  I am trying to create a SSIS package which will take excel source column content and will update/delete/insert into MySql , insert and delete is working fine but I am facing problem in update , i need to update a CREATED_TSTAMP column in mysql, date will not come in excel , so i created a variable with datetime datatype and trying to update date column in mysql using foreach loop container with execute sql task.

  I am getting below error when i try to use variable User::CREATED_TSTAMP as datetime and as string variable expression ( REVERSE( RIGHT(REVERSE( (DT_WSTR,30)(DT_DBTIMESTAMP)(getdate()) ) , 19))) :

  Error: The enumerator failed to retrieve element at index "3".

  Error: ForEach Variable Mapping number 3 to variable "User::PARTNER_SUPPORT_USER_ID" cannot be applied.

  if I remove User::CREATED_TSTAMP variable then everything is working fine without error. All the other mapping variables are string except User::CREATED_TSTAMP.

  Above error will not occur when i delete User::CREATED_TSTAMP and its mapping.

  Please help me with best solution ... i am spending 4 days but no solution

• Thom A

  SSC Guru

  Points: 99137

  More actions

  January 2, 2017 at 4:48 am

  #1920252

  Little confused firstly, are you using MySQL or SQL Server? You make several references to MySQL however this is a SQL Server forum, so which are you actually using? SSIS doesn't run on MySQL, so I want to make sure.

  I can't see a problem with your SSIS statement for you're timestamp variable, however, your error doesn't appear to be pointing at that but instead at your foreach containiner. What is the value/expression of your element at id 3? This is likelyfor your variable User::PARTNER_SUPPORT_USER_ID, as this is where your error is occuring.

  Also, as a side note, are you simply wishing to provide an inserted time for the row in your table? If so,why not use a DEFAULT value in your table, rather than passing through a value.

  Edit: This can be achieved in SQL Server and MySQL using the following:

  MySQL:

  --MySQL 5.6 onwards

  --I expect your should have this, as it was released in Febuary 2013.

  ALTER TABLE MyTable CHANGE Created_TStamp datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP';

  T-SQL:

  ALTER TABLE MyTable ADD CONSTRAINT DF_Created_TStamp DEFAULT (GETDATE()) FOR Created_TStamp

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  January 2, 2017 at 5:02 am

  #1920255

  Sorry Guys ... My colleague just added current_timestamp() (update table_name set date= current_timestamp(), column = ? where column = ?) in the date column. I triied creating a variable and tried to map that... but mapping doest nt happen for the variable.. Thanks. please close this thread