Home Forums Data Warehousing Integration Services SSIS Connection String by Project Variable RE: SSIS Connection String by Project Variable

  • October 17, 2018 at 6:56 am

    #2010037

    DinoRS - Wednesday, October 17, 2018 6:21 AM

    I was thinking of solving this by expression, how I understand things in this case as my project variable is read only I first have to read the project variable into a package variable. Then I can evaluate precedence constraints and for the daily load I get another package variable and read the project variable with the filepath from another user variable and add the file extension there, is that the right approach?
    Will the Filename Variable (File_YYYYMMDD.txt) be preserved in any case wether I have to run the Filename Variable for a full load (unchanged) or daily load (dynamically created) on a certain day? It might be not that cool if Filename get's changed once and afterwards full loads won't work.

    The 'project variable' you are referring to is actually a parameter. And, as you suggest, parameters are read only at runtime.
    So you need to create a variable which depends on the value of the parameter.
    If the parameter is 'full', set the variable to *.txt'.
    If the parameter is 'daily', set the variable to 'File_YYYYMMDD.txt' (where YYYYMMDD decodes to today's date)

    Finally, you need to set an expression in your FOREACH container which assigns the variable to the FileSpec property:

    This stuff all gets evaluated at run time. You do not need to worry about the property being wrongly persisted between runs, as long as you have set the parameter's value correctly.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.