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

Question

I have an SSIS package that the first task executes 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.

Answer 1

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: