SSIS - Automate an Execute Process Task with variable arguments

Peter Schmitz

Administrator
Staff member
Wouldn't it be nice if you could add some variables to your SSIS package, and use them throughout it? That way, if certain things, say, a file location, change, you will only have to change them in the variable, and not worry about editing multiple components.

Normally this is exactly how you would use variables, but with the Execute Process you cannot enter variables into the Arguments field, as they would be passed directly to the command window as is, and thus not supply the actual values you want to parse.

Luckily there's a workaround. You can use the Expressions page of the Component. Here's how to set up some arguments using this method. First up, double-click your Execute Process Task, and click on the "Expressions" page:

upload_2017-2-6_16-46-26.png


Then, click the ellipsis button to the right of the Expressions argument. This fires up the expressions editor window. In the left-side you can select which property you want to configure. Select "Arguments", and click the ellipsis in the Expression part to the right:

upload_2017-2-6_16-47-59.png


Here you can build your arguments by stringing together your pre-defined variables. In my example, I am feeding winscp with the following arguments in order for it to pop open a connection to our server, using a slew of variables holding the username, password, servername, port, and what not:

Code:
"/script=" +  @[User::local_path] + "\\download_files_with_parameters.txt" + " /parameter // \"" +  @[User::ftp_username] + "\" \"" +  @[User::ftp_password] + "\" \"" + @[User::ftp_servername] + "\" \"" + @[User::ftp_port] + "\" \"" + @[User::ftp_hostkey] + "\" \"" + @[User::ftp_path] + "\" \"" + @[User::local_path]  + "\""

Note the use of \" to ensure double quotes are passed on to the command line, wrapping all arguments in their own set of quotes.
 
Last edited:
Top