How to control SSIS package flow based on record c

I'm trying to first check if there are any new records to process before I execute my package. I have a bit field called "processed" in a SQL Server 2008 R2 table that has a value of 1 if processed and 0 if not.

I want to query it thus:

select count(processed) from dbo.AR_Sale where processed = 0

If the result is 0 I want to send an e-mail saying the records are not there. If greater than zero, I want to proceed with package execution. I am new to SSIS and can't seem to figure out what tool to use for this.

My package has a data flow item with an OLE DB connection inside it to the database. The connection uses a query to return the records. Unfortunately, the query completes successfully (as it should) even if there are no records to process. Here is the query:

Select * from dbo.AR_Sale where processed = 0

I copy these records to a data warehouse and then run another query to update the source table by changing the processed field from 0 to 1.
Any help would be greatly appreciated.

回答1:

One option would be to make use of precedence constraint in conjunction with Execute SQL task to achieve this functionality. Here is an example of how to achieve this in SSIS 2008 R2.

I created a simple table based on the information provided in the question.

Create table script:

CREATE TABLE dbo.AR_Sale(
    Id int NOT NULL IDENTITY PRIMARY KEY,
    Item varchar(30) NOT NULL,
    Price numeric(10, 2) NOT NULL,
    Processed bit NOT NULL
) 
GO

Then populated the new table with some sample data. You can see that one of the row has Processed flag set to zero.

Populate table script:

INSERT INTO dbo.AR_Sale (Item, Price, Processed) VALUES
    ('Item 1', 23.84, 1),
    ('Item 2', 72.19, 0),
    ('Item 3', 45.73, 1);

On the SSIS package, create the following two variables.

Processed of data type Int32
SQLFetchCount of data type String with value set to SELECT COUNT(Id) ProcessedCount FROM dbo.AR_Sale WHERE Processed = 0

On the SSIS project, create a OLE DB data source that points to the database of your choice. Add the data source to the package's connection manager. In this example, I have used named the data source as Practice.

On the package's Control Flow tab, drag and drop Execute SQL Task from the toolbox.

Configure the General page of the Execute SQL Task as shown below:

Give a proper Name, say Check pre-execution
Change ResultSet to Single row because the query returns a scalar value
Set the Connection to the OLE DB datasource, in this example Practice
Set the SQLSourceType to Variable because we will use the query stored in the variable
Set the SourceVariable to User::SQLFetchCount
Click Result Set page on the left section

Configure the Result Set page of the Execute SQL Task as shown below:

Click Add button to add a new variable which will store the count value returned by the query
Change the Result Name to 0 to indicate the first column value returned by query
Set the Variable Name to User::Processed
Click OK

On the package's Control Flow tab, drag and drop Send Mail Task and Data Flow Task from the toolbox. The Control Flow tab should look something like this:

Right-click on the green arrow that joins the Execute SQL task and Send Mail Task. Click Edit... the Green Arrow is called as Precedence Constraint.

On the Precedence Constraint Editor, perform the following steps:

Set Evaluation operation to Expression
Set the Expression to @[User::Processed] == 0. It means that take this path only when the variable Processed is set to zero.
Click OK

Right-click on the green arrow that joins the Execute SQL task and Data Flow Task. Click Edit... On the Precedence Constraint Editor, perform the following steps:

Set Evaluation operation to Expression
Set the Expression to @[User::Processed] != 0. It means that take this path only when the variable Processed is not set to zero.
Click OK

Control flow tab would look like this. You can configure the Send Mail Task to send email and the Data Flow Task to update the data according to your requirements.

When I execute the package with the data set to based on the populate table script, the package will execute the Data Flow Task because there is one row that is not processed.

When I execute the package after setting Processed flag to 1 on all the rows in the table using the script UPDATE dbo.AR_Sale SET Processed = 1, the package will execute the Send Mail Task.

Hope that helps.

回答2:

Your SSIS design should be

Src:

Select count(processed) Cnt from dbo.AR_Sale where processed = 0

Conditional Split stage [under data flow transformations]:

output1: Order 1, Name - EmailCnt, Condition - Cnt = 0
output2: Order 2, Name - ProcessRows, Condition - Cnt > 0

Output Links:

EmailCnt Link: Send email

ProcessRowsLink: DataFlowTask