Step by Step SSIS Package Deployment

SSIS Package Deployment

Providing a run able solution at testing or production generally we go for deployment (moving the developed application from one environment to another environment)
In SSIS there are two deployments.

a) File system deployment:
In this case the packages deployed to a file system (i.e., to a specified drive and folder).

b) SQL server deployment:
Here packages deployed in SQL server integration services.

  • To deploy the packages we require “manifest file”.
  • Manifest file contains the information which is important at the time of deployment.

Note: In real time we always perform the second type of (b) deployment (i.e., SQL server deployment).

  • It holds metadata information of package and its component. (Configurations, security, protection etc…)
  • When we built the project or solution manifest file updated created.

Manifest file Creation:

Solution explorer
 Rt click
Build: BIN


                Create deployment utility:
                Allow configuration changes:
                Create deployment utility: True
                Deployment output path: BINdeployment.

Build menu → build SSIS project
Go to solution → bin → deployment → observe manifest file.

MindMajix Youtube Channel

FREE Demo Class From Industry Experts - Enroll Now.!! MSBI training

a) File system deployment:

Go to manifest file → rt click → deploy → next → select file system deployment → specify folder to deploy → click next → next → finish.
Go to deployed folder and observe the package and configuration files deployed or not.
Package → rt click → open with → SQL server 2008 package execution utility → run (execute).

b) SQL server deployment:

Manifest file → rt click → deploy → next → Select SQL server deploymentàspecify
Server name: local host
Package path: maintenance plan
Click next → next → finish.
Go to SSMS → integration services → stored → Packages → MSDB → maintenance plan

Frequently Asked MSBI Interview Questions & Answers

Running packages:

Maintenance plan → packages → rt click → run package → execute.
Note: The property allow configuration true, ”allows configuration changes after the deployment”.
Applying security:
Two levels—
Bids level → password protection.
SSMS → Role base security

Password Protection

It helps us to prevent from

  1. Unauthorized deployment
  2. Unauthorized manipulation to the packages.

BIDS level for having better security; along with password we take “protection level also”.
Here protection levels available are:
a)      Don’t save sensitive
b)      Encrypt sensitive with user key.
c)       Encrypt sensitive with password.
d)      Encrypt all with password.
e)      Encrypt all with user key.
f)       Server storage.

Sensitive information

Generally package connection strings, user defined variables, enumerators…are considered as sensitive information.
→  Go to control flow → rt click → properties → security →
In package level password option assign.password → in protection level option select “Encrypt all with password” option.
“Build solution” and test in either of the ways
a)By opening the solution again.
b) By deploying the manifest file.
In the above two situations it asks password.

SSMS level security:
A user or group assigned to a role and every role will have responsibilities, so the users act according to the responsibilities.

Creating user/Group:
My computer → rt click → manager → local users and group → users → rt click → new user.
User name: VINAY → click ok.

SSMS level

Start menu → SSMS → Database engine → security → logins → rt click → new login.
Login name → search → vinay → ok.
Click ok.
System databases → MSDB → security → users → rt click → new user
User name: VINAY user
Login name: Rawanvinay
Check the required owned schemas and role members.
Eg: check DB-Data reader, DB-Backup operator..etc.
Sol: SSMS → integration services → connect → stored packages → MSDB → data collector → rt click on any package → click package roles → specify the roles in the reader and writer sections.

Explore MSBI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

List of Related Microsoft Certification Courses:

 SSRS Power BI
 SSAS SQL Server
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator


Course Schedule
MSBI TrainingJun 22 to Jul 07View Details
MSBI TrainingJun 25 to Jul 10View Details
MSBI TrainingJun 29 to Jul 14View Details
MSBI TrainingJul 02 to Jul 17View Details
Last updated: 03 Apr 2023
About Author

As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.

read less
  1. Share:
MSBI Articles