Post reply

ForLoop to check any row is present in the source table

  • May 23, 2012 at 3:48 am

    #261889

    Hi,

    I'm using a ExecuteSQL Task within a ForLoop. I need the ForLoop to keep on executing till any row is returned by the ExecuteSQL task. The query in ExecuteSQL task is Select Count(*) from Table1.

    For this I have created a UserVariable @RowCount. In ExecuteSQL the Resultset is set to @RowCount.

    In the ForLoop, EvalExpression is @Rowcount>0 and InitExpression, AssignExpressoin are not set. So that ForLoop is success when some rows are present in the table1 and ForLoop will keep on executing when no row is present in the table1.

    Now, even there is no row in the table, ForLoop is successful. I'm not understanding the behavior and how to resolve same.

    Please help or suggest some pointers.

    Thanks

    Regards

    Jim

  • May 23, 2012 at 4:04 am

    #1491647

    Jim

    You need to do more than set the value of a variable in T-SQL. You need to get it into an SSIS variable. The best way of doing that is with a query like SELECT ? = COUNT(*) FROM Table1 (better still, create a stored procedure to return the row count from the table with an output parameter) and parameter mapping in your Execute SQL task.

    John

  • May 23, 2012 at 7:22 am

    #1491743

    Thanks John,

    I'm facing a issue. The package is throwing an error

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "Rowcount": "The type of the value being assigned to variable "User::Rowcount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    ".

    @Rowcount is Int32.

    If I point to MS SQL server database, it is working fine, but not with Oracle.

    Please suggest what should be the data type I should set for the @Rowcount variable or in the ExecuteSQL task SQL query what casting I should do so that it works for oracle. I tried few but not succeeded 🙁

    Please help.

  • May 23, 2012 at 7:27 am

    #1491746

    I'm afraid I've never worked with Oracle. Is there anything in the Oracle syntax that allows an explicit conversion to an Int32-equivalent type?

    John

  • May 23, 2012 at 7:57 am

    #1491774

    I tried select Cast(count(*) as int) from crc.crc_ds1_td. It works in oracle by ExecuteSql task is throwing the same error 🙁

  • May 23, 2012 at 8:28 pm

    #1492229

    Jim1234 (5/23/2012)

    I tried select Cast(count(*) as int) from crc.crc_ds1_td. It works in oracle by ExecuteSql task is throwing the same error 🙁

    I don't know if it'll fix your particular problem but you must use all caps for Oracle objects. It's just the nature of the beasts.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 29, 2012 at 2:28 am

    #1494014

    Hi,

    The problem is fixed. I have used @RowCount==(DT_STR,50,1252)"0" in the EvalExpression in Forloop and it is working.

    I have a request. Is it possible to kill the SSIS package at runtime.

    Scenario,

    Package starts at 5.00 AM. Checking for availability of data and if not available ForLoop continues to execute. But if the data is not available after 3hrs of package execution then the package must be killed.

    Please let me know how to implement this.

    Thanks

    Regards

    Jim

  • May 29, 2012 at 2:32 am

    #1494016

    Jim

    Connect to the Integration Services server in SSMS, expand the list of running packages, and stop the one that is over-running.

    Edit: that's not the best way of doing it on a regular basis. What you need is to break out of the For Loop. Put another condition in the loop, checking that the difference between the current time and ContainerStartTime is less than three hours.

    John

  • May 29, 2012 at 2:50 am

    #1494025

    Hi John,

    The package execution should be stopped automatically. Looking for a solution which could be implemented inside the package or the package is schedule in SQLServer agent by the DBA. Is there a way in Agent to schedule it automatically to kill a package which is running for more than 3 hrs and send a mail to inform the same.

    Regards

    Jim

Viewing 9 posts - 1 through 8 (of 8 total)

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