The correct logic

[Abba]

Hello,

[SQL2000]

Iam syncing data from PROD to BETA using an SSIS package. After I load the
data to stage table from PROD, I compare the dates between stage and load
table. If the stage table only has latest data then only I need the rest of
the steps in the package to be executed.

So, in my SQL Execute task, I write:

IF EXISTS(SELECT 1 FROM Stage WHERE upd_date <= (SELECT MAX(upd_date) FROM
load))
BEGIN

RAISERROR('There is no recent data in PROD', 11, 1)

END

This works fine as it errors out when we dont have latest PROD data, but
then the scheduled job that runs the SSIS package always completes as
'failed'. How can I change the query or logic to accomplish what I want?


TIA,
AbbA

[Tom Moreau]

You can use conditional logic in SSIS. For example, your Execute SQL task
could set a value to 0 or 1 based on your SELECT. Then create a precedence
constraint from the Execute SQL task to the next task. Edit the precedence
constraint to use evaluation operation 'Expression and constraint'; Value
'success' and write the expression like @[User::RecentData]==1.
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Abba" <***@aaa.com> wrote in message news:***@TK2MSFTNGP04.phx.gbl...
Hello,

[SQL2000]

Iam syncing data from PROD to BETA using an SSIS package. After I load the
data to stage table from PROD, I compare the dates between stage and load
table. If the stage table only has latest data then only I need the rest of
the steps in the package to be executed.

So, in my SQL Execute task, I write:

IF EXISTS(SELECT 1 FROM Stage WHERE upd_date <= (SELECT MAX(upd_date) FROM
load))
BEGIN

RAISERROR('There is no recent data in PROD', 11, 1)

END

This works fine as it errors out when we dont have latest PROD data, but
then the scheduled job that runs the SSIS package always completes as
'failed'. How can I change the query or logic to accomplish what I want?


TIA,
AbbA