This code works in the SSIS editor, SSMS and running from the catalog.
Here is the code:
declare @maxDate as date
select @maxDate = cast(max([Date]) as date)
from [dbo].[sometable]
--print @maxDate
declare @currDate as date
select @currDate = cast(getdate() as date) ;
--print @currDate
if @maxDate <> @currDate
BEGIN
THROW 55000, 'Staging has not run for today!',1 ;
END
Error is:
When the query is run in SSMS the result will look something like the one shown in Figure1 below.
Figure 1 - Results in SSMS running original query
There is something being returned to the messages tab.
If you use an Execute SQL task in SSIS (the example here is from Visual Studio 2015). When you select that task you can select to return a results set, which can be a single or full results set. See figure 2 for where those options are set.
Figure 2 - shows where select which type of results set to return.
Based on the error message the Execute SQL task is expecting results set to be returned. The results set is the data returned in the results tab when the query is run in SSMS. See Figure 3 for an example of a one row, one field results set.
Figure 3 - Amended query returning a single row result set
Once the task has been configured to return a results set you can work with the dataset. For example, assign the single value to an SSIS variable and take actions based on that value. This article gives some ideas of what can be done, or this answer.
