Post reply

If rowcount = 0, email me.

  • October 30, 2009 at 2:20 pm

    #156362

    I am sure this has been asked before.

    I am evaluating some packages...and want to have SSIS e-mail me if the rowcount of a table = 0.

    I have no idea how to go about doing this.

    Anyone done it before?

  • October 30, 2009 at 11:16 pm

    #1073470

    You bet, it's pretty simple to do.

    Set up a package-scoped variable of type Int32 to store your row count. In your data flow, drag across a row count transformation - it's a pass-through transform that will capture the count of rows sent through it, and you'll assign that row count to the variable you just created.

    Now over in your control flow, create your send mail task and configure it with the appropriate info (to addr, from addr, and the message you want to receive). Connect your data flow to this new send mail task with a precedence constraint, and double-click this constraint to open its properties. Set it to "Expression", and in your expression box you should check whether the row count equals zero. The syntax will look something like this:

    @[User::MyRowCountVariable] == 0

    hth,

    Tim

    Tim Mitchell
    TimMitchell.net | @tmitch.net | Tyleris.com
    ETL Best Practices

  • November 9, 2009 at 8:46 am

    #1076677

    Thanks....Where in the data flow should I place the row count? Between the source and destination? Before the source? After the Destination?

  • November 9, 2009 at 9:00 am

    #1076689

    Between source and destination.

  • November 9, 2009 at 9:03 am

    #1076694

    Last question --

    Is there a way to do this without a dataflow task?

  • November 9, 2009 at 9:40 am

    #1076736

    Yes - you can assign the result of an execute SQL task such as

    SELECT COUNT(ID) FROM TABLE

    directly to a variable.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply