Post reply

If count of records in query >0, proceed, else stop SSIS task and email

  • July 28, 2014 at 9:53 am

    #289069

    Hello, I'm new to SSIS and trying to figure out this seemingly simply task......what would be just a few lines of VB code but seems very complex in SSIS.

    I have an OLEDB connection to an MS access database. I have an ExecuteSQL task that gets the count of a named query in the database. It runs Ok. What I want is for the entire package to go one direction if there are records in the query...go another direction if there are no records.

    How can I do this in the simplest fashion?

  • July 28, 2014 at 12:06 pm

    #1732966

    Hi There

    Simplest for me would be the following:

    Create a variable and populate the result to that variable

    a) Right click on your control flow surface and select "Variables"

    b) Create a "global variable" called accessCount (int)

    c) On your ExecuteSQL task double click and under "General/Result set" select "Single Row"

    d) Under "Result Set" tab click "Add"

    e) Set "Result Name" to the name of the field that has the result you are looking for. Click the variables drop down and select "user::accessCount"

    Let's presume that you will route to a data flow task to load data and to an email if there is no data.

    a) Drag successful precedent (green) from the executeSQLtask to the data flow task

    b) Right click the precedent and select "Edit"

    c) Under "Evaluation Option" select "Expression and Constraint"

    d) Leave "Value" as "Success"

    e) Under "Expression" type @accessCount > 0 (Click "Test" to make sure everything is working)

    Email

    Repeat above b), c) & d). on e) Enter the following expression @accessCount == 0

    Thus, if you have data you will process it and if you do not you will fire off an email to someone to let them know.

    SSIS can seem cumbersome but once you get into it; it really becomes a powerful tool.

    Hope this helps! 🙂

    [font="Arial Narrow"]MCITP Sql Server 2008: Business Intelligence. 4 Years +
    Jason Vollaire[/font]

  • July 28, 2014 at 1:06 pm

    #1732986

    Thank you! I had seen some [extremely] complex solutions on stack overflow and I was starting to think there was a simpler, variable-driven way.

    Thanks.

  • July 29, 2014 at 12:23 am

    #1733087

    No problem, glad it was what you were looking for.:cool:

    [font="Arial Narrow"]MCITP Sql Server 2008: Business Intelligence. 4 Years +
    Jason Vollaire[/font]

  • June 23, 2015 at 4:20 pm

    #1807590

    Great Instructions! 😀

Viewing 5 posts - 1 through 4 (of 4 total)

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