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 12»»

Ideal SSIS Package deployment Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 7:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Hello SSIS Masters, I'm currently going through the learning phase. I have two questions regarding package deployments:

Q1. What is the ideal mechanism to deploye the packages (and which one is GENERALLY fllowed across organizations) :
a. By using deployment utility
b. From within BIDS or Management Studio
c. From a command line using dtutil or xcopy

Q2. Where should you ideally place the your packages (and where are these GENERALLY stored across organizations):
a. in msdb database in SQL Server instance
b. in SQL Server instance
b. in file system

The question might seem foolish but i'm new and any clarification will help.
Post #1487242
Posted Thursday, August 22, 2013 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
--Edit: reply removed as did not answer question!


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1487270
Posted Thursday, August 22, 2013 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 10,193, Visits: 13,120
Q1: I'd try to use the same deployment process as used for other artifacts in my company. For example, if a deployment utility is used to deploy applications, use the same method to deploy SSIS packages (if possible).

Q2: This is a question that raises debate among most SSIS folks. Some love the file system, some love msdb. You need to find what works best for your organization. There are plusses and minuses to both. I tend to lean toward the file system, but I'm not anti-msdb.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1487297
Posted Thursday, August 22, 2013 6:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 169, Visits: 353
Definitely not a "master", but I will take a stab since you mention "generally"

Most of what I have worked..

Q1 - deployment utility

Q2 - File system
Post #1487604
Posted Thursday, August 22, 2013 10:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
If you are using SSIS 2012, you should also be aware of the new project deployment model. Some fairly detailed info here.

If you use it, you don't get any choice about the location of the packages (they go into SQL Server - a new database called the SSIS Catalog). You will find lots of resources on-line regarding pros and cons of using it. For me, there aren't many cons, though it takes a while to get used to the idea that single packages can no longer be deployed in isolation, if you're familiar with the pre-2012 way of doing things.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1487631
Posted Friday, August 23, 2013 1:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Thanks Jack/whereisSQL for the replies. Thats why I used the term "generally" as I wanted to know how the things are done MOSTLY in the organizations. As i don't have any experience and going through the learning curve, I have no idea on that.

I understand that if you are joining a team which already has entire setup done, then it makes your work easy as you have to work accordingly. But say there is new requirement coming up in your project and everything has to be worked from scratch, then which should be ideal one (from that perspective).

Though I got the idea from the discussion here that deploying through deployment utility to the file system seems one good OPTION.

Phil, thanks for providing your view on SSIS 2012. I am currently learning on SSIS 2008, so will learn that as a part of upgrading to SSIS 2012.

Though it may sound different and difficult, are there any case studies of SSIS implementation ? (it may be foolish question again but I want to learn as how practically the things are wokring in real time scenario )
Post #1487659
Posted Friday, August 23, 2013 1:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 12,962, Visits: 10,734
sqlnaive (8/22/2013)
Hello SSIS Masters, I'm currently going through the learning phase. I have two questions regarding package deployments:

Q1. What is the ideal mechanism to deploye the packages (and which one is GENERALLY fllowed across organizations) :
a. By using deployment utility
b. From within BIDS or Management Studio
c. From a command line using dtutil or xcopy

Q2. Where should you ideally place the your packages (and where are these GENERALLY stored across organizations):
a. in msdb database in SQL Server instance
b. in SQL Server instance
b. in file system

The question might seem foolish but i'm new and any clarification will help.


Q1: c. You can script out a deployment with dtutil in a batch file. The deployment utility might be an option, but I never use it myself. Certainly not from BIDS (which is only possible if you install BIDSHelper by the way, or if you use SSIS 2012).

Q2: a. Definately in the MSDB database (this is a personal choice. There's no right or wrong here). The advantage here is that the packages are protected by SQL Server security.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1487660
Posted Friday, August 23, 2013 2:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Thanks a lot Koen.

Are there any case studies released by MS for SSIS implementation in big scenarios ? Considering the current demand of ETL tools and the extensive competition in the market, MS should release it which might give the technicians like me help getting better understanding of the approaches made at the professional level plus it can be a very good marketing strategy as well. Many organizations might consider using SSIS as their ETL tools after going through such successful case studies.
Post #1487679
Posted Friday, August 23, 2013 2:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 12,962, Visits: 10,734
This might be a good starting point:

Extract, Transform, and Load (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1487684
Posted Tuesday, August 27, 2013 1:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Hi Koen, I have one simple question and maybe I'm missing something here. I have two VM machines created and in one (kind of dev), I have created one solution with one project having some 30 packages within. Now I was trying to deploy this to another machine using deployment utility. So I performed all the necessary steps and created the SSISManifest file as well. I copied all the contents in the folder along with the SSISmanifest file to the other machine (much like prod). There i ran the manifest file and performed all the steps. But my question is:

a. now how can i see them in prod BIDS ?

b. what if later i want to do a change only in few of the packages (say 5 out of total 30 packages) in dev env and deploy them to the now productionized project (i mean replacing the current one in production from the new one with latest changes in dev).
Post #1488591
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse