Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Packages - Best Practice Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 9:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 10:58 AM
Points: 23, Visits: 41
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!
Post #1403684
Posted Monday, January 7, 2013 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1403702
Posted Monday, January 7, 2013 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 10:58 AM
Points: 23, Visits: 41
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?
Post #1403768
Posted Monday, January 7, 2013 11:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1403777
Posted Monday, January 7, 2013 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 10:58 AM
Points: 23, Visits: 41
Ok, thanks for the suggestions!
Post #1403797
Posted Monday, January 7, 2013 12:53 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:01 AM
Points: 656, Visits: 3,960
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.

Post #1403830
Posted Sunday, January 13, 2013 1:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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/ 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
Post #1406510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse