June 4, 2018 at 8:30 am
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.
June 4, 2018 at 10:08 am
zech 55624 - Monday, June 4, 2018 8:30 AMHi, 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 consideringI'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.
If these are hard-coded Ids which cannot be identified dynamically, in my opinion, they should be added to a physical table and looked-up from there.
If the Ids can be generated from a query of existing data (and I don't mean 'select Id = 1 union all select Id = 2' etc etc!), then I would consider putting them in a view.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 6, 2018 at 8:27 am
There isn't a way to dynamically grab the product IDs as they are just added sequentially with other product IDs that aren't relevant to my process. So a table does seem the most efficient solution. Thanks for taking the time to give me some advice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply