How To Achieve User Input At Run-time with SSIS Package

• YSLGuru

  SSC-Insane

  Points: 21669

  More actions

  June 9, 2008 at 12:54 pm

  #380991

  I'm not a SSIS Guru so I apolagize if this is an obvious one to most but is there any way to get User Input at Run-time for an executing SSIS package?

  For example let's say I have package that runs for data (via basic SELECT) dated between a start and an End date and I want the ability to provide the package with this info via something like an input box, how do I make this happen? I've found how to set up variables and pass their values to the T-SQL my package executes but I can't determine how to get the package to ask for that info and maybe that's because SSIS is incapae of this.

  Thoughts?

  Thanks

  Kindest Regards,

  Just say No to Facebook!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  Follow

  June 9, 2008 at 1:14 pm

  #826291

  I don't believe you can. SSIS packages are designed to run as scheduled jobs independent of users.

  You may be able to write a small front end app that asks for the input then executes the package on the server with that value.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  June 9, 2008 at 1:18 pm

  #826295

  You can set values of variables and other package properties through the command line execution of a package. Here is a sample I sanitized a bit:

  [font="Courier New"]/SQL " " /REPORTING E[/font]

  You can also do this through the SSIS object model - with basically the same flexibility. As an alternative (if you have a lot to pass in) you can create a table and read the table in the package to setup your variables.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  June 9, 2008 at 1:19 pm

  #826296

  My posting got sanitized.

  setting variables from the command line looks like:

  /SET "\Package.Variables[User::gvSourceId].Value";101

• pduplessis-723389

  SSChampion

  Points: 10599

  More actions

  June 10, 2008 at 12:34 am

  #826460

  Or alternatively, if your variables are stored in SQL configuration, you could set them in your configuration table, and then call the DTExec which will read from your config table...

  Little note though, know what your variable is stored as. Example:

  Configuration Filter: "Archive directory name"

  Configured Value : "YourValueHere" [This is what you will most probably store from your inputbox]

  PackagePath : "\Package.Variables[User::varArchiveDirectoryName].Properties[Value]" [You would most probably keep this hidden but use this when you update the configured value]

  Good luck!

  ~PD