Search code examples
sql-serverssissql-server-2008-r2sql-agent-job

How does a variable like `User::filePath` get passed to a script in an SSIS package that's run by an SQL Agent Job?

If I look directly at the script, the call to sp_add_jobstep when using CREATE SCRIPT for this is:

@command=N'/FILE "<local-server-path-to.dtsx-file>" /CONNECTION "FlatFileConnection;"<unc-path-to.csv-file>" /CHECKPOINTING OFF /REPORTING E'

So it seems to me that the <unc-path-to.csv-file> somehow specifies the value of User::filePath or maybe the FlatFileConnection within the SSIS package.

The Job Step Properties for the SQL Agent job (in the UI) under General and then the Command line tab, have the Restore the original options selected, so where does the User::filePath get specified?

Solution

  • The following line of code:

    @command=N'/FILE "<local-server-path-to.dtsx-file>" /CONNECTION "FlatFileConnection;"<unc-path-to.csv-file>" /CHECKPOINTING OFF /REPORTING E'

    is the arguments passed to the dtexec utility which is the used to execute SSIS packages.

    You can simply pass the [User::FilePath] value using the following line of code:

    /SET \Package.Variables[User::FilePath].Properties[Value];"C:\File.dtsx"

    The whole command will be:

    @command=N'/FILE "<local-server-path-to.dtsx-file>" /CONNECTION "FlatFileConnection;"<unc-path-to.csv-file>" /CHECKPOINTING OFF /REPORTING E /SET \Package.Variables[User::FilePath].Properties[Value];"C:\File.dtsx"'

    Referring to the dtexec Utility documentation:

    /Set Overrides the configuration of a variable, property, container, log provider, Foreach enumerator, or connection within a package. When this option is used, /Set changes the propertyPath argument to the value specified. Multiple /Set options can be specified.

    The following is an example of executing a package and providing a new value for a variable:

    dtexec /f mypackage.dtsx /set \package.variables[myvariable].Value;myvalue