0

We are building an SSIS package with a Fuzzy Logic transform which needs to be called from a stored procedure.

There are a few articles on calling an SSIS package from a stored procedure, but what we need to do is:

  1. Not only call it, but pass a parameter (a name) to the Fuzzy Logic transform

  2. Return the result, or part of it, to the calling code

Mostly concerned with 1 right now, as I can't find a way to pass a parameter from the stored procedure to the Fuzzy Logic Transform in the SSIS package.

Any ideas appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mark Gregory
  • 73
  • 6
  • This is not a good idea. I suggest you build your fuzzy logic inside your stored procedure _or_ run the whole thing from the package. You _can_ call external process from within T-SQL (in this case the DTEXEC.EXE process) but there are issues around, for example, what happens when you call the external process once for every row in a million row dataset? What happens if the external process fails or has a memory leak? – Nick.Mc Apr 08 '18 at 22:52
  • To run a package from T-SQL, you can put it in a SQL Agent job and start it with sp_start_job. Or you can use `xp_cmdshell` to call DTEXEC.EXE. Or you can wrap up a call and embed it in T-SQL using SQL CLR. _None_ of these are particularly recommended. To pass a parameter _in_, you need to pass it into the package on the DTEXEC command line using the `SET` switch: https://dwhanalytics.wordpress.com/tag/pass-parameter-from-command-line-to-ssis-package/ – Nick.Mc Apr 08 '18 at 22:56
  • Getting a result out is much more difficult. You pretty much need to write the result to a table then read it back, which is pretty unreliable for multiple process calls. – Nick.Mc Apr 08 '18 at 22:57
  • Thank you Nick, I think that will get me there. – Mark Gregory Apr 09 '18 at 19:43

0 Answers0