Post reply

SET SSIS variable expression at runtime e.g. foreach-loop

  • June 17, 2015 at 7:27 am

    #319163

    Hi,

    I was wondering if there might be another solultion to following:

    I have a big bunch of SQLs that have to be fired in one SSIS Package.

    This list of SQLs will be expended regularly.

    So that I do not have to deploy this package every time, that a SQL will be added

    and for clearity reasons I moved the SQLs to a table.

    The SQL Variable is build via an expressions as they use other variables from the package.

    So in that table I save in fact expressions of the SSIS SQL variable.

    However foreach-loops only allow me to insert into a value of a variable.

    So as an act from necessity

    I

    - created two variables -> var_sql_expression + var_sql (EvaluateASExpression = True)

    - inserted the values in the variable var_sql_expression

    - created a script-task that copies the value of var_sql_expression to the Expression of var_sql (C# Skript can address the Expression)

    - Executed SQL from var_sql

    The Expression will be Evaluated at runtime.

    Is there a simpler way to realize this e.g. without skript task?

    Cheers,

    Christian

  • June 17, 2015 at 8:56 am

    #1806093

    In my opinion, a simpler approach would be to create a stored proc which contains all the sql statements you wish to execute. That would simplify the SSIS package as you only have to execute the stored procedure.

  • June 19, 2015 at 5:28 am

    #1806628

    It would work, when the sql writes the result in a table instead of using it in a dataflow.

    Properly it would get quite confusing, when you have 20-30 page size big sql after another

Viewing 3 posts - 1 through 2 (of 2 total)

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