none
Datetime conversion issue in SSIS

    Question

  • I have created a variable BEGIN_TMSTP with datetime datatype

    Further, I have created two execute sql task.

    Execute sql task 1:

    select sysdate as begin_tmstp from dual

    AND in the result set im mapping like                 Result Name             Variable Name

                                                                              begin_tmstp        User::BEGIN_TMSTP

    Execute SQL task 2:

    insert into batch_job_log(JOB_NAME,BEGIN_TMSTP)VALUES(?,?)

    Mapping looks like:

    System::PackageName           VARCHAR

    User::BEGIN_TMSTP                Date

    Until now, it is inserting correct date and time.

    I want to use a query from a variable but it is giving me conversion error:The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

    The Query from avariable looks like as follow:

    "select name,address from  table1 , table 2 where a.code = b.code and b.txnmy_last_change_tmstp> (select MAX(begin_tmstp)from lbs_stage.batch_job_log)
    and b.txnmy_last_change_tmstp< ('"+ @[User::Begin_Tmstp] +"','YYYYMMDDHH24MISS')"

    Please Advice.

    mardi 28 février 2012 01:34

Réponses

  • Sorry Harry

    If I understand you have the Execute sql task 1 populating the variable User::BEGIN_TMSTP, which at the moment is a string using TOCHAR(SYSDATE,'YYYYMMDDHH24MISS')

    You likely have adapted/disabled Execute sql task 2 to work past the variable which is now a string

    And the third task has a query based on a variable that is

    "select name,address from  table1 , table 2 where a.code = b.code and b.txnmy_last_change_tmstp> (select MAX(begin_tmstp)from lbs_stage.batch_job_log)
    and b.txnmy_last_change_tmstp< TO_DATE('"+ @[User::Begin_Tmstp] +"','YYYYMMDDHH24MISS')"

    It all appears fine unless someone else can point out the fault :(


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!


    • Modifié GGoldspink mardi 28 février 2012 05:19
    • Marqué comme réponse Harry Jaj mardi 28 février 2012 19:39
    mardi 28 février 2012 05:19

Toutes les réponses

  • The value for @[User::Begin_Tmstp] is a datetime, what it wants is for it to be a DT_WSTR.

    What you can do instead is move your SQL function into your expression using DatePart

    http://msdn.microsoft.com/en-us/library/ms137586.aspx

    so

    and b.txnmy_last_chagne_tmstp < '" + DATEPART(yyyy,@[User::Begin_Tmstp]) + DATEPART(mm,@[User::Begin_Tmstp]) 

    + DATEPART(dd,@[User::Begin_Tmstp]) 

    + DATEPART(mm,@[User::Begin_Tmstp]) 

    + DATEPART(Hh,@[User::Begin_Tmstp]) 

    + DATEPART(mi,@[User::Begin_Tmstp]) 

    + DATEPART(ss,@[User::Begin_Tmstp]) 

    + "'"


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    mardi 28 février 2012 02:21
  • could try something along the following lines in a Script Task. Pass in User::dtmBegin_Tmstp (Read) & User::strBegin_Tmstp (ReadWrite)

    Dim dtmMyDateTime As DateTime
            dtmMyDateTime = DateTime.Parse(Dts.Variables("User::dtmBegin_Tmstp").Value.ToString())
            Dts.Variables("User::strBegin_Tmstp").Value = dtmMyDateTime.ToString("yyyy-MM-dd HH:mm:ss tt")

    HTH,
    Tejas

    mardi 28 février 2012 02:53
  • Hi, thanks for the reply

    your suggestions sounds really great. I will go with first advice as i am not too familiar with second suggestion. 

    Can you please tell me how can i get the date in the form '23-FEB-12 04.56.08.000000000 PM'

    This is the date from  oracle database. I am asking because i want to compare the two dates to establish a date range.

    Thanks

    mardi 28 février 2012 03:23
  • Thats looking specific, and outside of the datepart function, can your source selection cast it as a varchar?  Then you use that to populated a string variable as opposed to a date?

    Or look into Bytebugs suggestion if the ToString supports the format you are after.

    Would oracle to_date function help you be more flexible with the incoming string?

    http://www.techonthenet.com/oracle/functions/to_date.php


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!


    • Modifié GGoldspink mardi 28 février 2012 03:34
    mardi 28 février 2012 03:29
  • Why do you need the month as Feb?  The To_date function can adapt to numbers?

    Just realised that was likely why your original query had the brackets and date format,

    to_date ('"+ @[User::Begin_Tmstp] +"','YYYYMMDDHH24MISS')"


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    mardi 28 février 2012 03:39
  • when i use 

    select to_char(sysdate) from dual in first execute sql task and put the value in a variable

    and then in second execute sql task i insert the value of variable 

    i get the error as variable cannot be found.

    I know variable is there and i double check the spelling too.

    No idea why this is happening.

    Then i also used bytebugs suggestion

    "select name, address
    from  table 1, table 2 where a.code = b.code 
    and b.txnmy_last_change_tmstp> (select MAX(begin_tmstp)from lbs_stage.batch_job_log)
    and  b.txnmy_last_change_tmstp<"+(DT_STR,4,1252)DATEPART( "yyyy" ,@[User::BEGIN_TMSTP]  )  + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" ,@[User::BEGIN_TMSTP]  ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" ,@[User::BEGIN_TMSTP] ]   ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[User::BEGIN_TMSTP]  ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" ,@[User::BEGIN_TMSTP]   ), 2) + 
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" ,@[User::BEGIN_TMSTP]   ), 2)+""

    But this expression is not evaluating.

    Please let me know how to get rid of this issue.


    mardi 28 février 2012 04:00
  • Double check your scope of the Variable, it may be just scoped for the first task.

    Also another thread that jumped to the top highlighted this issue

    http://connect.microsoft.com/SQLServer/feedback/details/351786/ssis-datetime-variable-precision

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b82952b8-6b47-4091-91be-fccf7ee7c66f

    Which may be another reason to convert your date to a string representation rather than using the datetime variable type in SSIS


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!


    • Modifié GGoldspink mardi 28 février 2012 04:16
    mardi 28 février 2012 04:14
  • I am trying your  suggestion to cast the source selection as varchar. I wrote:

    select to_char(sysdate,'yyyymmddhh24miss') as tmstp from dual

    I changed the datatype of variable Begin_Tmstp from datetime to varchar.

    The first sql task runs good and store the value in the above variable.

    But in the second sql task where i am inserting this value in the table, i got error as:

    " failed with the following error: "ORA-01843: not a valid month"

    Can you give any suggestion on this please

    mardi 28 février 2012 04:23
  • alas do not have Oracle in front of me, but forum post on that exact issue

    https://forums.oracle.com/forums/thread.jspa?threadID=884046

    Taking from that if your select does

    to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS'),

    Then your conversion back should be

    to_date(" + @[User::Begin_Tmstp] +",'dd/mm/yyyy HH:MI:SS')

    Check the to_date, as it was missing in your first initial post


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!



    • Modifié GGoldspink mardi 28 février 2012 04:35 added + to function line
    mardi 28 février 2012 04:33
  • THERE is some prob. I am nor sure , i am not able to insert the value in the table where i convert the source selection to varchar
    mardi 28 février 2012 04:54
  • Sorry Harry

    If I understand you have the Execute sql task 1 populating the variable User::BEGIN_TMSTP, which at the moment is a string using TOCHAR(SYSDATE,'YYYYMMDDHH24MISS')

    You likely have adapted/disabled Execute sql task 2 to work past the variable which is now a string

    And the third task has a query based on a variable that is

    "select name,address from  table1 , table 2 where a.code = b.code and b.txnmy_last_change_tmstp> (select MAX(begin_tmstp)from lbs_stage.batch_job_log)
    and b.txnmy_last_change_tmstp< TO_DATE('"+ @[User::Begin_Tmstp] +"','YYYYMMDDHH24MISS')"

    It all appears fine unless someone else can point out the fault :(


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!


    • Modifié GGoldspink mardi 28 février 2012 05:19
    • Marqué comme réponse Harry Jaj mardi 28 février 2012 19:39
    mardi 28 février 2012 05:19
  • GGoldspink, you was spot on..

    It is working perfectly fine now. The expression is running great.

    Your help is much appreciated.

    Just a small help more, Second execute sql task is not inserting the datetime in the table.

    i have tried to map the Begin_tmstp variable to both varchar and date variable.but it is not going through.

    Any suggestion

    mardi 28 février 2012 05:59
  • I think its the problem with yyyymmdd (the order in which it is written), and the table is storing date as ddmmyy.

    May be this is the prob. not sure though.

    mardi 28 février 2012 06:19
  • So Execute SQL 2 is not working?

    insert into batch_job_log(JOB_NAME,BEGIN_TMSTP)VALUES(?,?)

    If still in Oracle, and steps 1 and 3 are now fine with the string representation, then what is the structure of the table Batch_job_log?

    If datetime in oracle you will need to do exactly as you are doing in 3, likely though as

    insert into batch_job_log(JOB_NAME,BEGIN_TMSTP)VALUES(TO_DATE(?,'YYYYMMDDHH24MISS'),?)

    If it's SQL Server, it'll be best to use the string representation 'YYYY-MM-DD hh24:MI:SS'


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    mardi 28 février 2012 06:33
  • insert into batch_job_log(JOB_NAME,BEGIN_TMSTP)VALUES(TO_DATE(?,'YYYYMMDDHH24MISS'),?)

    I could not under stand this statement.

    What will be the mapping of first ? and second ?

    mardi 28 février 2012 07:06
  • BTW, the datetime format in  batch_job_log table is 

    23-FEB-12 04.51.28.000000000 PM

    mardi 28 février 2012 07:09
  • I got that code from your first post

    Execute SQL task 2:

    insert into batch_job_log(JOB_NAME,BEGIN_TMSTP)VALUES(?,?)

    Could you post the create table statement for batch_job_log table.  You will have to use a tool to access the oracle, yell if you need help with that

    Also please explain what you have done with execute SQL task 2.  how does it populate its SQL? etc


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    mardi 28 février 2012 11:13
  • I want to insert package name and the value of the Begin_Tmstp variable that we created in execute sql task 1.

    Package name will come from system variable (no problem with that). but can you help me to insert the value of begin_tmstp variable in the batch_job_log table.

    mardi 28 février 2012 17:21
  • Kool. Its working.

    I am using

    insert into batch_job_log(begin_tmstp,job_name)VALUES(TO_DATE(?,'YYYYMMDDHH24MISS'),?)

    and mapping 1st ? to besin_tmstp

    and 2nd ? to package name system variable. Is that rht.

    Now just the last step in the package. I want to log the end time of the package.

    I am using

    Update update batch_job_log
    set end_tmstp = sysdate,status = 'Success' where Begin_Tmstp = ?

    AND i am mapping this ? to begin_tmstp variable. But it giving error. I guess it is not finding that value of begin_tmstp variable in the table to update it with end_tmstp.

    Please Advice.

     

    mardi 28 février 2012 17:42
  • That is working too now.Your help is much much appreciated.

    TO log end time, i am using:

    update batch_job_log
    set end_tmstp = sysdate,status = 'Success' where Begin_Tmstp =TO_DATE(?,'YYYYMMDDHH24MISS')

    and mapping the ? to begin_tmstp variable as varchar.

    Let me know if this is correct.

    mardi 28 février 2012 17:49
  • It all looks fine


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    mardi 28 février 2012 22:51
  • Thanks again friend.

    mercredi 29 février 2012 00:45