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
     ↓
  Project
     ↓
 Rt click
     ↓
 Properties
Build: BIN

Deployment:

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

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

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.
Execution:
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.
Observing:
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.

List of Related Microsoft Certification Courses: