2

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
leeand00
  • 25,510
  • 39
  • 140
  • 297
  • 2
    check the following links 1- https://stackoverflow.com/questions/48511517/is-it-possible-to-pass-parameters-to-a-dtsx-package-on-the-command-line 2- https://socalsql.com/2010/08/30/passing-parameters-into-ssis-from-a-sql-job/ – Yahfoufi May 15 '19 at 14:26
  • 1
    "somehow specifies the value of User::filePath" No it doesn't it just sets some properties on a connection manager. You might have some logic in your package that in turn sets the value of `User::filePath` but we don't know. – Nick.Mc May 16 '19 at 09:00

1 Answers1

1

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

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124