Post reply

Evaluate variable and dynamically set Connection Manager Expression

  • July 6, 2009 at 7:11 am

    #207886

    My Connection Manager called ExcelConnecton (to an Excel connection) is wired to an Expression that points to a Directory & Filename. I'm using a .dtsConfig file to set pieces of my connections.

    I have a situation where I need to interrogate the value of a variable received then based on that value use 1 of 2 different connections:

    For example,

    if the value of variableA is "1" then I'll use this in my Connection Manager's connection's Expression:

    @[User::Excel_DIR] + @[User::EXCEL_SubDIR_A] + @[User::BaseDate] + ".xls"

    if the value of variableA is "2" then I'll use this in my Connection manager's connection's Expression:

    @[User::Excel_DIR] + @[User::EXCEL_SubDIR_B] + @[User::BaseDate] + ".xls"

    ANY recommendations on how to perform this check and dymanically set my Connection Manager's connection?

    BT

  • July 6, 2009 at 12:13 pm

    #1020548

    At first glance, I'd think about setting up two connection managers for the Excel files, one with the path being the first expression, the other with the path being the second expression. Then set the DelayValidation property of both connection managers to True, because if only one file exists at a time, you don't want SSIS to try and pre-validate the existence of both files before runtime and throw an error.

    Use precedence constraints in the control flow to direct the control to one of two data flow tasks, depending on the variable value. The source adapter in each of the data flow tasks is the appropriate of the two connection managers. Then set the DelayValidation property of each of the Data Flow Tasks to be true as well, so SSIS does not try and pre-validate this before runtime as well.

  • July 7, 2009 at 3:33 pm

    #1021360

    DBASkippack (7/6/2009)

    My Connection Manager called ExcelConnecton (to an Excel connection) is wired to an Expression that points to a Directory & Filename. I'm using a .dtsConfig file to set pieces of my connections.

    I have a situation where I need to interrogate the value of a variable received then based on that value use 1 of 2 different connections:

    For example,

    if the value of variableA is "1" then I'll use this in my Connection Manager's connection's Expression:

    @[User::Excel_DIR] + @[User::EXCEL_SubDIR_A] + @[User::BaseDate] + ".xls"

    if the value of variableA is "2" then I'll use this in my Connection manager's connection's Expression:

    @[User::Excel_DIR] + @[User::EXCEL_SubDIR_B] + @[User::BaseDate] + ".xls"

    ANY recommendations on how to perform this check and dymanically set my Connection Manager's connection?

    Try this:

    @[User::Excel_DIR] + (@[User::variableA] == "1" ? @[User::EXCEL_SubDIR_A] : @[User::EXCEL_SubDIR_B]) + @[User::BaseDate] + ".xls"

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • July 8, 2009 at 3:55 am

    #1021546

    thank you !

    BT

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

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