January 1, 2017 at 10:34 am
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
January 2, 2017 at 4:48 am
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
January 2, 2017 at 5:02 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply