November 27, 2012 at 4:04 pm
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?
Thanks
Lempster
March 20, 2014 at 4:54 pm
Did you set the DelayValidation property of the Excel connection manager to true?
March 21, 2014 at 2:34 am
messages4kanthi (3/20/2014)
Did you set the DelayValidation property of the Excel connection manager to true?
Yes, I clearly stated that in my original post. This is an old thread and I can't remember exactly what I did to resolve the problem; I think it may have been to do with the order in which my variables were set, i.e. I was trying to use the value of a variable that had not yet had its value set by other expressions.
Anyway, it's all working nicely now. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply