August 22, 2013 at 7:20 am
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.
August 22, 2013 at 7:54 am
--Edit: reply removed as did not answer question!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 22, 2013 at 8:17 am
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
Consultant - Straight Path Solutions
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
August 22, 2013 at 6:47 pm
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
August 22, 2013 at 10:29 pm
If you are using SSIS 2012, you should also be aware of the new project deployment model. Some fairly detailed info here[/url].
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 23, 2013 at 1:44 am
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 )
August 23, 2013 at 1:45 am
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 instanceb. 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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 23, 2013 at 2:21 am
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.
August 23, 2013 at 2:34 am
This might be a good starting point:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2013 at 1:45 am
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).
August 27, 2013 at 1:50 am
Where did you deploy them to? Which version of SSIS are you using?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2013 at 1:53 am
Both in 2008. I deployed them to file system. (Not the default path but to new folder with the project name )
August 27, 2013 at 2:00 am
If you deployed them to the filesystem, you can see them by going to that directory.
If you deploy to the filesystem (and not in MSDB or the SSIS package folder), you don't need to use the deployment wizard.
As they're just files, you can copy them from one directory to another.
If you only want to change a few packages, just copy those new packages to the server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2013 at 2:13 am
Thanks for the second answer Koen. Thanks cool. On first one, let me check once again today and get back by tomorrow. 🙂
July 19, 2015 at 12:30 am
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 instanceb. in file system
The question might seem foolish but i'm new and any clarification will help.
In my opinion, this decision would be driven by kind of environment you are supporting and number of ssis package you have to support.
You can loosely say, SSIS is not instance aware.
In case you are having Huge Boxes with multiple SQL Server instances created on them, then msdb would not be a good choice as SSIS packages for all instance would be saved under one msdb.
For instance : you have 4 SQL instances being hosted on a box and you choose msdb as a storeage area for your packages. In this scenario, SSIS packages belonging to all instances would be hosted in msdb of one instance only, you can chose this instance by updating .xml file.
So , in this scenario it would be better to store ssis packages as file shares.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply