The point is that several factors will decide where you keep your queries. While you may make small changes from time to time, chances are you'll pick a standard and stick with it.
I was prompted to write this recently when I came across a best practice suggesting you store queries in package parameters. In theory, if the package has to be ported to another server, you could pass it in with slightly different query code that's specialized for that server; it's supposed to make the package execution more dynamic without having to redeploy the package. Also, it should be really easy to change the query if needed.
I found it hard to believe that this would be an actual best practice -- it just doesn't seem to be workable enough across the different scenarios to be useful that often. For starters, let's take the problem of the large query. In the above example where the query is very, very large, you certainly wouldn't want to pass this into the package at runtime as a parameter -- if you even could.
Here's a quick list of the problems I have with this best practice:
- Queries can easily be too long to reasonably pass into a parameter.
- It's too easy to easy to accidentally change something in the job.
- You can’t easily look at the package and know what params are being used. You have to look at the job too.
SSIS metadata is really touchy, and if you change something in the query, you could throw the metadata out of whack. Not having to verify the package after a change can cause it to fail when it next runs. This isn’t necessarily a problem when you’re changing a condition, but if you’re changing one of the columns returned, you could throw the metadata out of sync.
Let me get this straight: If I change the where clause, I won’t have to open the package. Correct. How about if I change one of the columns? It depends on what you do. How about if I add a function to one of the columns? Same thing, it depends. This sounds too complicated to me. Can I just store it either in the database or in the package and just open the package every time? Sure, but it's a little cumbersome, isn’t it?
And again, I’m not saying you can’t do this -- but you need to be aware of what you’re getting yourself into. This is the same with all best practices. They're developed for a reason, but the reason is never given, so you’re stuck with figuring out what it is for yourself.
I’ve heard a best practice defined as something that works 51 percent of the time. That’s a reasonable explanation, but this best practice doesn’t hold up under such scrutiny. There’s no way that 51 percent of your processes will benefit from this standard.