SSIS – Part 5
SSIS Interview Questions and Answers for Experienced and Fresher’s
SSIS Interview Questions and Answers Part 5
Here we are publishing series of posts on SSIS Interview questions and answers Part 5 for experienced and fresher’s. Below is the series 5.
Q. What are the SSIS package protection levels?
Ans:
There are 6 different types of protection levels.
- Do not save sensitive – (When exporting using DTUTIL specify for protection- 0)
- Encrypt sensitive with user key – 1
- Encrypt sensitive with password – 2
- Encrypt all with password -3
- Encrypt all with user key – 4
- Rely on server storage
Do not save sensitive: makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.
Encrypt sensitive with user key: Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails.
Encrypt sensitive with password: Uses a password to encrypt only the values of sensitive properties in the package. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails.
Encrypt all with password: Uses a password to encrypt the whole package. The user must provide the package password. Without the password the user cannot access or run the package.
Encrypt all with user key: Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility
Rely on server storage: Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database.
When it is time to deploy the packages, you have to change the protection level to one that does not depend on the developer’s user key. Therefore you typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. Encrypt the packages by assigning a temporary strong password that is also known to the operations team in the production environment.
Q. What are the phases of execution of a package when running with DTEXEC?
Ans:
- Command sourcing phase: The command prompt reads the list of options and arguments
- Package load phase: The package specified by the /SQL, /FILE, or /DTS option is loaded.
- Configuration phase: Options are processed in this order:
- Options that set package flags, variables, and properties.
- Options that verify the package version and build.
- Options that configure the run-time behavior of the utility, such as reporting.
- Validation and execution phase: The package is run, or validated without running if the /VALIDATE option is specified.
Continue reading →
