How to modify the variable in SSIS?

问题:

I have a simple String variable with the following value: "C:\Test.txt". Now I would like to edit the variable to point to a different file. I cannot find a way to do that. I can change the Name, Data Type, but not the value itself! Do I need to delete the variable and create the new one?

Update: The problem was caused by "ReadOnly" property set to "True". For typical scenarios, see the accepted answer below.

回答1:

If you want to change it in designer just right click on free space and --> Variables.

But if you want to change it at runtime I suggest you to:

1. create script task
2. choose language
3. add your variable to ReadWriteVariables.
4. Edit script.

5. For example in VB:

   Dts.Variables("myVariable").Value = @"C:\Test2.txt";

   Dts.TaskResult = ScriptResults.Success

回答2:

As @Yuck and @devarc have noted, there are two different and distinct values a Variable holds. The Design-time value is the value you assign when the variable is first created. In your case, the variable holds C:\Test.txt as the design-time value. Everytime you open the package, it would show C:\Test.txt until you change it in the

To make the value of a variable change while the package is running, your options are either to set the value or calculate it. Here I have created a package-level variable CurrentFile with the value of C:\Test.txt

One thing that often trips people up is that they have correctly changed the run-time value but when they run it in BIDS, they see the "old" value. The value displayed in the Variables window does not change during package execution.

During package execution, my Variables window still shows the design-time value (C:\Test.txt) but the true value is reflected in the Locals window (C:\Test2.txt)

Setting a value

The value of most anything in SSIS can be established at run-time through a set of verbose command-line options or through configuration sources. The biggest difference in my mind is that this approach is that the value will always be the value for the entire lifetime of package execution. Sequential or parallel invocations of a package can change that value but for that execution the value would remain constant (barring an explicit modification of the value.

/SET

Command-line execution (dtexec.exe), right clicking on a package and running from the filesystem (dtexecUI.exe) or creating a SQL Agent job step of SQL Server Integration Services all allow for providing a run-time value through the SET command. Using the above variable, the following command would set the run-time value to C:\Test2.txt

dtexec /file C:\Generated.dtsx /set \Package.Variables[User::CurrentFile].Properties[Value];"C:\Test2.txt"

Configuration

SSIS offers an option to create configuration sources to provide run-time values to packages. The article I linked to above does a much better job describing the pros and cons of the configuration options than I will do here. I will say that I typically use both - my SET command configures a connection manager which is then used by the package to find the "full" set of package configurations.

Calculating a value

There are a variety of tasks in SSIS that can change the value of a variable as well as the use of Expressions to change a value. I see these as things that operate on value whilst the package is in flight.

Tasks

A Script Task is one of the most commonly used mechanisms for those starting out but I find other tools in the SSIS toolkit usually better suited for changing variable values.

Foreach Loop Container and Execute SQL Task are two of the other big Tasks you should look at for assignment of a variable value.

Expressions

Expressions are the most glorious candy in the SSIS toolbox. Most every "thing" in SSIS exposes properties for configuration. That's helpful, but using assigning an expression to build those properties is outstanding.

For example, imagine 3 variables RootFolder, FileName and ComputedCurrentFile with values of C:\, File2.txt and empty string. On the Properties window for ComputedCurrentFile we'd change the value for EvaluateAsExpression from False to True and then use an expression like @[User::RootFolder]+ "\\" +@[User::FileName] That simply concatenates the value the first two variables together. This can be helpful if the file name for processing was standard but the source folder changed often. Or if we're talking about output, it's common to use expressions to build an output file name using the date and possibly time of when the package is running.

Finally, there is nothing that prevents a mixing and matching of these approaches. I typically use a configuration to point a file enumerator at the correct starting folder and then use calculated values to identify the current file for processing.