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.
Note: In real time we always perform the second type of (b) deployment (i.e., SQL server 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.
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).
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
Maintenance plan → packages → rt click → run package → execute.
Note: The property allow configuration true, ”allows configuration changes after the deployment”.
Bids level → password protection.
SSMS → Role base security
It helps us to prevent from
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.
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.
My computer → rt click → manager → local users and group → users → rt click → new user.
User name: VINAY → click ok.
Start menu → SSMS → Database engine → security → logins → rt click → new login.
Login name → search → vinay → 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.
Get Updates on Tech posts, Interview & Certification questions and training schedules