I have developed an SSIS package (using BIDS 2005) that requires almost everything to be set dynamically; Connections Managers, package variables, SQL statements executed by Execute SQL tasks etc.
The reason for this is that the package is designed to be a generic utility package that produces Excel files in various directories and is called from a stored procedure. The stored procedure sets some of the package variables; others are set using an xml variable. It is based on this excellent article by Jeff Wharton.
The Excel Connection Manager is set using a package variable and that variable in turn is constructed from a number of other variables. Everything works fine when those other variables are hard-coded, but if one of them is itself set dynamically, the package fails with an error which basically says that the Excel Connection Manager cannot be acquired because it is incorrectly formed.
I have set the 'Delay Validation' property on the Excel Connection Manager to True, but this doesn't seem to make a difference.
To better illustrate the situation, my Excel Connection Manager's connection string is set, via an Expression to use a variable, User::Conn_ExcelFile
The User::Conn_ExcelFile variable has its EvaluateAsExpression property set to True and the expression it uses is:
@[User::vDriver] + @[User::vExtendedProperties] + @[User::vExcelFile]
The User::vDriver and User::vExtendedProperties variables are hard-coded, but the User::vExcelFile variable is set dynamically using a /SET option in the dtexec command line.
I can see using breakpoints that the User::vExcelFile variable is indeed being correctly set to the value specified by the /SET option, but the value is blank when the User::Conn_ExcelFile variable is set.
Q: Are variables set dynamically in the order in which they are created (in which case I can resolve this easily by ensuring that my User::Conn_ExcelFile appears further down the list of variables than my User::vExcelFile variable)?
Q:Is it even possible to create this chain of dynamically-set variables?