How can I stop a package execution based on a stored procedure output?

I have an SSIS package that the first task e开发者_运维知识库xecutes a stored procedure to verify that the run date is not a holiday. If it is a holiday, then it returns a record set with a count of 1.

I want to be able to stop the SSIS if the recordcount is 1 but continue to run if the recordcount is zero. I don't know the best way to implement this. What control flow item should I add to the package?

I am relatively new to SSIS so I don't know what item to add. Any help would be great.

Here is a possible option that can give you an idea to achieve this. The example checks if today's date is a holiday by checking against a table containing list of holidays. Rest of the package tasks will execute only if today's date is not a holiday. The example uses SSIS 2008 R2 and SQL Server 2008 R2 database.

Step-by-step process:

1. Create a table named dbo.Holidays and stored procedure named dbo.CheckTodayIsHoliday using the script given under SQL Scripts section. Populate the table as shown in screenshot #1.

2. On the SSIS package, create two variables named RecordCount and SQLProcedure. Populate them with values as shown in screenshot #2. also, create an OLE DB Connection to connect to SQL Server database. I have named it as SQLServer in this example. Refer screenshot #3. The example uses Data Source instead of normal connection. That's why the icon is different in the screen shot.

3. On the SSIS package, place a Data Flow task and within the data flow task place an OLE DB source and Row count transformation. Refer screenshot #4.

4. Configure the OLE DB Source as shown in screenshots #5 and #6. This will execute the stored procedure and fetch the results.

5. Configure the Row count transformation as shown in screenshot #7.

6. On the Control Flow, I have placed few more dummy tasks as shown in screenshot #8.

7. Right-click on the connector between the Data Flow Task and the next task (Sequence Container) as shown in screenshot #9.

8. Configure the Precedence Constraint Editor as shown in screenshot #10.

9. Screenshot #11 shows package execution with today's date (June 16, 2011) present in the dbo.Holidays table marked as holiday. Of course, June 16, 2011 is not a holiday where I work unless I take a vacation.

10. Change the table data as shown in screenshot #12.

11. Screenshot #13 shows package execution with today's date (June 16, 2011) not present in the dbo.Holidays table.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[Holidays](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [HolidayDate] [datetime] NULL,
 CONSTRAINT [PK_Holidays] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[CheckTodayIsHoliday]
AS
BEGIN

    SET NOCOUNT ON

    SELECT  HolidayDate
    FROM    dbo.Holidays
    WHERE   DATEDIFF(DAY, HolidayDate, GETDATE()) = 0   
END
GO

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Well one way is to create an Execute SQl task to use that you use to set the value of variable @Holiday. Then change the Success flow line coming out that Execute SQl task to both success and a constraint by right clicking on the green line itself and clicking edit. Choose Expression and Constraint as the evaluation operation and then add an expression something like the below for the expression:

@Holiday == 0