SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Packages - Best Practice


SSIS Packages - Best Practice

Author
Message
ShorePatrol
ShorePatrol
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 53
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!
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58243 Visits: 9730
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
ShorePatrol
ShorePatrol
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 53
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?
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58243 Visits: 9730
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
ShorePatrol
ShorePatrol
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 53
Ok, thanks for the suggestions!
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3196 Visits: 4794
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40034 Visits: 14412
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search