January 7, 2013 at 9:01 am
Folks,
I'm new to SSIS (2008 R2), and I've developed a handful of packages and want to know if I'm "doing it right"...
I have BIDS installed on the SQL server, and the SSIS packages are saved to the filesystem, also on the SQL server....
To create a scheduled job on the SQL server, you need the package to point to, and with connections, etc, it just seemed easier to keep it all on the server in one place (and back it all up to tape daily)...
Do DBA's typically install BIDs on their development machines? If so, where do they save the packages to, their local file system, the SQL server file system, or the SQL database (MSDB database)...???
Any insight?
Thanks!
January 7, 2013 at 9:24 am
I create a dev environment that parallels the production environment, in terms of disk names, windows domains, etc. Then develop on there.
I usually deploy the packages to msdb instead of the file system, so that the packages are backed up along with the SQL Agent jobs that run them. There are pros and cons to that, and deploying to the file system is certainly a valid option, it's just not my preference.
I also use source control for the packages, in BIDS, so that I can track versions, etc.
Build on dev, deploy and test to QA, then to production via staging, is my prefered path.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 11:15 am
Thanks - the problem for me is, I have my development machine, and the production server - that's it....
Do you see any problem with my continuing to create SSIS packages on the production server?
January 7, 2013 at 11:32 am
Yes. It's development in production. It will bite you, sooner or later.
Develop on the dev machine. Get a copy of SQL Server Developer Edition ($43 on Amazon), if you haven't got one already. if you have to, set up a virtual machine on the dev box, so you can parallel things like drive letters and network connections, and use that.
Let your boss know (if that's not you) that you need dev and QA machines, even if they're virtual servers. There will be costs, but, in the long run, they'll save money.
First time you run an SSIS package that accidentally deletes the wrong data or loads malformed data into a table, or dumps the junk data in the real table and the good data in a trashbin table, or whatever, it'll either result in nothing bad at all, if it's on a dev/QA server, or in potential job loss and financial problems, etc., for you and the company, if it's in production.
Prior small company I worked for let some devs work on the production database. One of them accidentally updated every customer's password (essentially locking all the customers out of the whole website) when he didn't select all the lines of his update statement and thus left out the Where clause. Simple mousing error. Huge problem.
So, yeah, it's a bad thing to develop on the production server. Too easy to commit database suicide.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 12:01 pm
Ok, thanks for the suggestions!
January 7, 2013 at 12:53 pm
Consider making you packages dynamic using Package Configurations.
http://msdn.microsoft.com/en-us/library/cc895212.aspx
This could be important as far as disaster recovery where you server names, file locations and even database names may be different.
January 13, 2013 at 1:32 pm
You can develop in and against production if you like but it is not recommend and typically will not bring you the best results in terms of releasing quality software and avoiding catastrophes like a major data loss or an outage.
Some general tips:
- Get your code into a version control system and make sure it is backed up daily. Better yet, get yourself a free 5-user cloud edition of TFS 2012 Express Edition from http://tfs.visualstudio.com/[/url] and then you do not even have to worry about backing it up.
- Use Package Configurations of some kind. I prefer either XML config files or SQL Server storage but there are many viable options. The underlying principle to aim for is "the same code everywhere." If you need to run a package in a dev environment to try and recreate a bug with test data then you should not have to change the code (i.e. the .dtsx file) to make that happen. You should be able to change a config file or a value in a database table to have the package operate on the dev environment instead of production. The reverse direction is true as well. If you decide to develop in dev and then deploy to prod (which I would recommend) then you do not want to have to change the .dtsx file after it has been tested and validated in dev.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply