Execute Sql Task

Question

Post reply

Execute Sql Task

forum member

Ten Centuries

Points: 1290
More actions
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

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

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

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 1

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.

mcha7628 SSC Veteran Points: 265 More actions · Answer 2

mcha7628

SSC Veteran

Points: 265

September 3, 2009 at 11:02 am

#1048927

Please post the error message

sqlrunner Mr or Mrs. 500 Points: 513 More actions · Answer 3

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

Tim Mitchell SSCoach Points: 15671 More actions · Answer 4

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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices