0

I've put together an SSIS package and deployed it to the SQL server. This package needs to be run on demand and needs to values passed into it, a batch number and the user ID of the person requesting the data. I've spent a fair amount researching how to pass the values in and can't find anything that I'd be able to use. The simplest would be to do xp_cmdshell from a stored procedure or call DTEXEC directly from the application, but these can't be used in this instance. The company has decided that xp_cmdshell is too much of a security risk and not every user is going to have the necessary permissions to call DTEXEC directly nor does the company have the necessary licenses for installing the binaries on every user's computer and having the package run locally.

All of this leads to the question: How do I pass values into the SSIS package?

I've looked at using a SQL Job as they can modify the variables within the package, but then I'm stuck with having to get the values into the Job and haven't found any way of doing this directly. I've seen it recommended to use a table and have the package go get values from there, but that leaves the question of how to handle multiple people trying to run the package at the same time.

Also, in case anyone is wondering what the package is doing, it is doing a bunch of Fuzzy Lookups and since we are only using SQL Standard, I don't have the option of doing the Fuzzy Stuff from within T-SQL via Master Data Services. Ideas?

Brad
  • 272
  • 2
  • 7
  • 22
  • One way of doing it would be to use the [table as a queue](http://rusanu.com/2010/03/26/using-tables-as-queues/). So The first step it just dequeues the top item and processes it. You won't get multiple instances of the package running unless you have multiple SQL Agent jobs anyway though. – Martin Smith Sep 24 '14 at 22:19
  • Does this website answer your question: http://www.bidn.com/blogs/DevinKnight/ssis/1655/passing-values-into-an-ssis-package-at-runtime-from-outside-the-package? – Code Different Sep 24 '14 at 23:55
  • 1
    You're also not going to have the option of using the Fuzzy components as those are Enterprise Edition features. [Supported features by edition](http://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx) Fuzzy stuff is about 55% of the way down the page – billinkc Sep 25 '14 at 02:45
  • billinkc - that is probably referring to the fuzzy capabilities within T-SQL. SSIS has had fuzzy capabilities at least back to the 2005 version as per http://msdn.microsoft.com/en-us/library/ms137786%28v=sql.100%29.aspx Zoff Dino - that was the site I found on how to pass values into a package from a job, but I can't find anything on passing values into a job. Martin Smith - I thought about that, but I'm having issues in ironing out the logic within the package so that if submitted queries came in very close to each other that one wouldn't get missed. – Brad Sep 25 '14 at 12:37
  • 2
    billinkc's comment was about **edition** (Enterprise/Standard/Express etc) not version (2005/2008/2012 etc). Note that developer edition has same featureset as Enterprise Edition so you may not hit the issue until you deploy. – Martin Smith Sep 25 '14 at 12:45
  • Well hell... I could have swore that it was different between the T-SQL fuzzy command that were available in Enterprise versus SSIS itself. I was wrong. – Brad Sep 25 '14 at 15:28

1 Answers1

0

So it turns out that SQL Standard can't use the Fuzzy features at all, whether T-SQL or SSIS (Thanks, billinkc, for pointing that out). So the short answer is that we're going back to the drawing board on how to have our data searching needs met.

Brad
  • 272
  • 2
  • 7
  • 22