Hi ALL I want to insert a value in oracle table with ssis using ssis expression in execute swl task: "Insert into batch_job_log (end_tmstp) values"+(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2)+"" Somehow the expression is evaluated correctly as: Insert into batch_job_log (end_tmstp) values20120109101444 but when i execute the packarge, i got the error as: [Execute SQL Task] Error: Executing the query "Insert into batch_job_log (end_tmstp) values201201..." failed with the following error: "ORA-00926: missing VALUES keyword". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any advise how to corrct it. Thanks

Seems you missed the space between the 'values' and the number, it must be values 20120109101444. Fix by adding a space to the dynamic expression values "Arthur My Blog

Hi This is in regard to my last post. I manages to write the expression but i got the new error now like: [Execute SQL Task] Error: Executing the query "Insert into batch_job_log (end_tmstp) values ('201..." failed with the following error: "Conversion failed when converting date and/or time from character string.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. My EXPRESSION IS: "Insert into batch_job_log (end_tmstp) values ('"+(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2)+"')" and its is evaluating as: Insert into batch_job_log (end_tmstp) values ('20120109101444') I appreciate your suggestions. Thanks

Try this: "Insert into batch_job_log (end_tmstp) values ('"+(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + '-' + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + '-' + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + ' ' + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + ':' + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + ':' + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2)+"')" The value '20120109101444' isn't a valid datetime value. the above should evaluate to: "Insert into batch_job_log (end_tmstp) values ('2012-01-09 10:14:44')". '2012-01-09 10:14:44' is a valid datetime.

i tried but the error is: single quotation mark was not expected. Please advice

Sorry about that. It should be: "Insert into batch_job_log (end_tmstp) values ('"+(DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + " " + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + ":" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + ":" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2)+"')" SSIS expressions require double quotes

Hi DEAR Thanks for your time and help. I used your expression and it works fine when i insert into sql server database. I t gives me error of invalid mnth because i want to insert into oracle database and it has the datetime format as : 20-DEC-11 02.35.58.130000000 PM Can you help me on that too. Thanks Harry

I'm not going to do the whole thing for you. This is pretty close. I don't know what Oracle will actually accept so you may have to change the hour format and also add the AM/PM at the end. You'll also need the case for month if it requires the three digit abbreviation instead of the month in integer form. "Insert into batch_job_log (end_tmstp) values ('"+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) + "." + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ms" , @[System::StartTime] ), 2) + "0000000" + "')" Hope that helps some. Lefka

hi, May i suggest using oracle to_date function in your insert statement, e.g.: "Insert into batch_job_log (end_tmstp) values (TO_DATE('"+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) + "','DD-MM-YY HH24.MI.SS'))" In this way you don't need to worry about the date format of the Oracle Server. Hope this helps. ~ J.

Your idea really works for me. Thanks a lot. I would appreciate if you can spend little more of your time on my one problem. I am using a select statement in oledb source (from a variable). I am putting the below query in a variable (it is a select statement with a where clause from one date to another). I am getting through this expression. Can you have a check on it please. "select TestRecordtype, request_id from department where LOAD_TMSTP between (select max(END_TMSTP) LOG) and TO_DATE("+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +",'DD-MM-YY HH24.MI.SS') " Thanks Harry

hi harry, i think there is a missing "FROM" and single quotes. "SELECT TestRecordtype, request_id <br/>FROM department <br/>WHERE LOAD_TMSTP BETWEEN (SELECT MAX(END_TMSTP) FROM LOG) <br/>AND TO_DATE('"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +"','DD-MM-YY HH24.MI.SS')" Let me know how it goes. ~ J.