July 3, 2014 at 4:06 pm
As of 3 weeks ago I have finally been able to get access to SQL Server 2012, so I am just now getting to play with some of the features. My new company has multiple ways of calling an SSIS package, and some (if not all) use Package Parameters. I just did a little research on these, and I in general get how they work. What I am curious about is what everyone thinks about them. Here is why I ask...
I liked how my last company had dynamic variables setup. They had all of the required variables stored on a single database table on a single server. This kept all of the connections strings, file paths, file names, etc. that a package would use. My computer, the UAT server, and the Prod server all had the same file in the same location with 1 character difference to note which environment to run in. This way I knew I would always use only the Dev server when I ran a package during development. I would also only touch the test folders with my test data. Once we moved it to UAT or Prod, that single character difference would point to a different set of variables for the same PackageID, and we didn't have to worry about forgetting to change hard coded variable values. The only 2 hard coded variable values were the PackageID and the variable holder database. I have heard of similar ways of doing this via an XML file, but I have not had the (ahem) pleasure of working with that yet.
Jump to my current job and why so far I have not been impressed with the Package Parameters. We have 3 different jobs that all call the same SSIS package. We feed in a different FacilityID, and using values stored in tables we know to look for different file names, etc. To me this seems to be a similar set up to how my last company had set the system. The big difference is my old company used a Script Task to verify which environment we were running in (I can see setting this for different FacilityID's, but that would be cumbersome), and my new company uses multiple tables across multiple databases (I know, this is not the best set up in the world, and we are fixing it soon) to decide what we will use for some of the variable values.
What are your thoughts on storing variables outside a package? Do you have any blog posts or articles you can link to show current best practices? Do I need to get over myself and learn this new design model, or are there a large number of you that don't like it as well (I just re-read this, and I have to apologize for the loaded question...lol)? And last but not least, do the Package Parameters only work via the Integrated Services Catalog, or can they be accessed for the File System packages too?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
July 3, 2014 at 4:24 pm
Still being in 2008, I'm not sure I can speak directly to Package Parameters.
However, I can speak to configurations in general, which is what it sounds like you're describing.
Personally, the usage of a local system variable to define where to pickup your configuration files (or SQL table, or whatever) has been the most obvious solution. In my current environment, we have a dedicated SSIS/ETL server that is dedicated to that function. All non-maintenance jobs go on this server (IE: Anything not backup, index rebuild, etc). On said server (one for each environment) is a database where we store our configuration table.
The System Variable tells the package to look at the correct configuration table for its calls. Similar to your initialization files, just less work overall for us, and easier querying of the settings.
For a package that needs to have a variable or something changed between runs, that's not something we'd configure, we'd use the Set Values tab in the SSIS command step to set that variable to what we'd needed and have 3 jobs (one for each choice) setup on their particular schedules.
I don't know if I answered your question, but that's how we do it. There's a lot of approaches to controlling configurations and the like, but it really depends on familiarity and consistency.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 4, 2014 at 12:37 am
Package parameters are only for the new project deployment model.
Regarding the configurations: there are lots of different ways to handle configurations within multiple environments. Choose one and stick with it in your company 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply