0

I am trying to create an SSIS package which pulls a set of records from the database by checking a date field (MODIFYDATE) in the table against a package variable (User::LastUpdate). Records where MODIFYDATE is equal to or after LastUpdate get selected.

I have tried this two ways, using DateDiff and a UDF that returns a bit (both are using Second as the interval), and have run into the same problem both ways. The date matching seems to be off by two days.

If LastUpdate is set at 1/22/2013 0:00, two records should be returned, one with a MODIFYDATE of 1/22/2013 14:47 and another with a MODIFYDATE of 1/22/2013 15:34. But neither of those gets returned unless LastUpdate is set no later than 1/20/2013 12:00. If it set earlier than that, they both get returned. If later, neither get returned.

The rows are being pulled in an OLE DB Source component in the SSIS package. If I go into the Query Builder and run the query from there, supplying the date manually, the correct rows are returned. I have inserted a breakpoint and confirmed that the LastUpdate contains the correct date when the rows are pulled (the value of LastUpdate is supplied by a stored procedure earlier in the package).

Here is the query as it stands now.

SELECT ACTIVITIES.*
FROM ACTIVITIES LEFT OUTER JOIN
ArchivedEvents ON ACTIVITIES.FBC_EVENTSID = 
    ArchivedEvents.FBC_EVENTSID
WHERE (dbo.IsDateInOrder(?, ACTIVITIES.MODIFYDATE) = 1) OR
(dbo.IsDateInOrder(?, ArchivedEvents.MODIFYDATE) = 1)

And the function I created 

CREATE FUNCTION [dbo].[IsDateInOrder] 
(
    @Date1 DateTime,
    @Date2 DateTime
)
RETURNS bit
AS
BEGIN
    DECLARE @retval bit

    if DATEDIFF(s, @Date1, @Date2) >= 0
        SET @retval = 1
    else
        SET @retval = 0

    RETURN @retval

END

GO
wildplasser
  • 43,142
  • 8
  • 66
  • 109
Hypersapien
  • 617
  • 2
  • 8
  • 23
  • Just so you're aware, by wrapping a function around a column as you do in your WHERE clause is going to force a table scan which can be rather costly. In-lining that logic will generally yield better performance. – billinkc Jan 23 '13 at 17:58
  • To verify, you have mapped that parameter twice, yes? OLE DB connection managers are ordinal based while an ADO.NET is going to use named parameters. – billinkc Jan 23 '13 at 18:00
  • @Billinkc - I'm not too worried about the cost. This is meant to be run a couple times a day, tops, and it only takes a few seconds to run. Yes, two different tables have to be checked, and it's the same date both times. I should have mentioned that in the description of the problem. – Hypersapien Jan 23 '13 at 18:04
  • Can you profile the proc call on the server, and see what is actually passed in? – Kevin Dahl Jan 23 '13 at 18:41
  • @Kevin - do you mean the UDF? That's something I was having a problem with. I'm not sure how to get that information. It doesn't let me write to a table from inside a function, or call a stored procedure that does, either. I'm not sure how to view the function running in real time. I'm using SQL Server 2008 R2 with Management Studio, if you can let me know how to do it. – Hypersapien Jan 23 '13 at 18:50
  • Under tools in SSMS, you'll find SQL Server Profiler. Connect to your DB with it, select the events you want to profile (likely SQL:StmtStarting, SQL:StmtCompleted), make sure the textdata column is checked off for both, run the trace, run your package. The output should then include what is actually being called on the db. You may have to modify the events you're profiling, and if you're running this on a busy system you may also have to set up filters (click on the title of the column in the event selection) to narrow down the scope - otherwise it's kind of like drinking from the firehose. – Kevin Dahl Jan 23 '13 at 19:07
  • RPC:Starting/Completed looks to be the event where it actually sets the variable for the date, just FYI. I also created a test similar to yours on my system and it works as expected. – Kevin Dahl Jan 23 '13 at 19:45
  • I tried running the the query from the Query Builder, as I described above, and found the date in RPC:Starting. But when I tried the same trace on the full package, I couldn't find it anywhere. – Hypersapien Jan 23 '13 at 21:36
  • Are you sure SSIS is running against the same database? If you didn't set any filters on the events for profiler you should see everything. – Kevin Dahl Jan 24 '13 at 00:03
  • Yes, I see tons of events coming specifically from the package, but none with the parameter values. – Hypersapien Jan 24 '13 at 17:07

0 Answers0