Post reply

Execute Sql Task

  • September 3, 2009 at 12:31 am

    #156169

    Hi,

    Need help with Execute Sql Task,I am using delete statement where it deletes records based on date

    DELETE FROM mytesttable

    WHERE (CONVERT(VARCHAR, datevar, 103) = ?

    I have used a input parameter whose data type is DBTime

    This input parameter is a user::variable whose datatype when i created was datetime but i cant find

    same datatype in the parameter mapping -dialog box under DataType option

    but the task is failing at comparision

    Please suggest

    Thanks

  • September 3, 2009 at 10:13 am

    #1048887

    DELETE FROM mytesttable

    WHERE (CONVERT(VARCHAR, datevar, 103) = ?

    I have used a input parameter whose data type is DBTime

    This input parameter is a user::variable whose datatype when i created was datetime but i cant find

    same datatype in the parameter mapping -dialog box under DataType option

    but the task is failing at comparision

    Please suggest

    Thanks

    There are several issues here. What is your datevar column datatype. I am guessing that it is datetime. If thats the case, you will never find a match in your where clause. This is what you get when u convert datetime to varchar with format 103 - 03/09/2009 and since your user variables is datetime it will be something like this - 7/14/2009 11:31 AM. they are never equal even if u have the same date. U have to convert both to just date, leaving the time portion out. ALso, what is your connection type for connection in execute sql task -OLE DB or ADO.NET? It matters, the way you map the input parameters. For OLE DB, mapping is doing by 0, 1 , 2, and so on...In ADO.NET you can actaully give paramater names.....like @date or so on.

    The easier way to do is --declare a user variable - lets say mydate of type varchar(15) and in the SSIS use execute sql task to get the date as varchar and format the way u did in your delete statement and retrun a singls string hold the value in the variable mydate.

    Pass this variable as input in your above sql command. make sure if u are using OLEDB connection ur parameter is set to 0.

  • September 3, 2009 at 11:02 am

    #1048927

    Please post the error message

  • September 4, 2009 at 9:10 am

    #1049456

    forum member (9/3/2009)

    Hi,

    Need help with Execute Sql Task,I am using delete statement where it deletes records based on date

    DELETE FROM mytesttable

    WHERE (CONVERT(VARCHAR, datevar, 103) = ?

    I have used a input parameter whose data type is DBTime

    This input parameter is a user::variable whose datatype when i created was datetime but i cant find

    same datatype in the parameter mapping -dialog box under DataType option

    but the task is failing at comparision

    Please suggest

    Thanks

    I noticed your varchar declaration does not have any size. Also, if you are doing a convert to 103, that is giving you dd/mm/yy, so I would simply make your incoming variable as a string, not dbtime.

    Josef Richberg
    2009 Exceptional DBA
    http://www.josef-richberg.squarespace.com
    http://twitter.com/sqlrunner

  • September 4, 2009 at 10:25 am

    #1049508

    The DT_DBTIME data type represents a time (hour, minute, second) and not a date. Comparing a mm/dd/yyyy value to a DT_DBTIME will almost always result in an error.

    Take a look at the DT_DBTIMESTAMP data type, which should work better for your purposes. You might consider just using a string as your input data type since you're converting the comparison value to a VARCHAR anyway, but be mindful that your query will not use indexes in the same way if you're converting dates to strings (assuming it's an indexed column).

    For reference, here's a listing of the various data types in SSIS: http://msdn.microsoft.com/en-us/library/ms141036.aspx

    hth,

    Tim

    Tim Mitchell
    TimMitchell.net | @tmitch.net | Tyleris.com
    ETL Best Practices

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply