Hi, all. I'm a relative newcomer to databases and ssis, but I'm learning quickly. This question is me trying to learn some best practices.
I'm going to be revising a SSIS package that runs throughout the day, scanning orders in our database and using t-sql to pull out lines related to a subset of products based primarily on those product IDs. Then it'll push those lines into another table and do this and that with those lines, which isn't relevant here. This subset of product ids might get used 2-3 times through the course of the ssis package in a few scripts.
There are lots of ways for me to handle that subset of product IDs. I believe the current package just has the product ids scripted into the t-sql as a where clause, which works fine, but seems cumbersome. (I didn't build them originally.)
Every year, we revise this package and add a few more product ids. Seems to me that there is a better long-term way of storing these. Which option makes most sense? (Note: I cannot really make alterations to the inventory table itself.)
- create a table that holds this subset of product ids (allowing me to add product ids to this table every year and being pretty much done)
- create a view in the database that pulls these products together (allowing me to just edit the view to add new products)
- create a table variable or cte in the script (this has me editing the t-sql, but the product ids are at least in a more convenient and easy-to-read place)
- some other option I'm not considering
I'm not too concerned with performance/efficiency here, as we're talking just 30-50 products and maybe 10-20K order lines over the course of a year. I'm just curious, theoretically, what's the best approach?
Thanks for you help. And let me know if this is better posted elsewhere.