Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

SSIS - Automate an Execute Process Task with variable arguments

Discussion in 'Business Intelligence' started by Peter Schmitz, Feb 6, 2017.

  1. Peter Schmitz

    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 (TSQL):
    "/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: May 16, 2017

Share This Page