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.
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
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.