SSIS Package Variable Set Order

Question

I have an ssis package where the variables are set by database table. In order to re-use code, this package will be run on a weekly basis. But there will also be a need for the use to run it ad hoc. Now I have the code to run the package from the command line, and I know how to set variable names using the switch /Set \Package.Variables[User::FileName].Properties[Value];"aaab"

My question: is there a way to set preferences on whether to use the table or the passed in value? Or is there a way to determine what was the executer, this may help if I can determine if it was run from .net vs a job vs a stored procedure then I can say use the passed in value?

Answer 1

There are many ways to implement runtime configuration of a package (which is what you're doing)

You don't mention what version, but have a read of this:

http://msdn.microsoft.com/en-us/library/cc671625.aspx

This for examples says configuration happens in this order for SQL 2005:

-Design time configurations are applied -Command line options are applied -Parent package configurations are applied

This means if your variables are assigned from the standard SSIS configuration table using standard configurations, then any command line options will overwrite them afterwards.

Have you considered that your user will need SSIS installed, and the package will execute on their local machine, including data round trips?

Answer 2

Try looking at System::UserName. You could write an expression that will set User::FileName one way if this is the weekly job being run by DOMAIN\SomeSpecialSQLServerAgentAccount, or else set User::FileName differently if the package is being executed manually by DOMAIN\JohnQPublic.

You could use variables for both versions of the filename, e.g., User::FileNameFromTable and User::FileNameFromCommandLine, and then pick one or the other based on System::UserName.

I'm afraid I haven't found a way to influence how a variable gets set (designtime initialization, configuration file, commandline configuration, job properties configuration).