Explore topic-wise InterviewSolutions in .

A PACKAGE that runs SUCCESSFULLY in BIDS and fails from the SQL agent job can mean that the SQL Agent jobs do not have the necessary permission for some connections of the package. We can either create a proxy account that allows SQL Agent jobs to run the package or elevate the current account permissions.

SSIS is a PLATFORM for integration services that provide automated tasks and various transformation components for cleaning, transforming, and validating data throughout the process of data loading from source to DESTINATION. In this article, we have seen what are the most commonly asked SSIS interview questions and the answers to them.

References and Resources:

SSIS Documentation

Database

SQL Interview Guide

Variables are used in script tasks to FACILITATE data exchange with other objects of the package. Script task makes use of the property called Variables of Dts object for reading and writing to VARIABLE objects of the package. To make the variables available to the CUSTOM script, we need to add them to read-only or read-write variable lists. The variable names are case-sensitive and care has to be taken while adding the variables to the list. We can use the VALUE property for reading and writing data to the variables.

The SSIS connection MANAGER has ISSUES in storing the password. Hence we can follow the below-proposed solutions:

• Method 1: Create and use SQL Server Agent Proxy account which uses credential that makes SQL Server Agent capable of the running job as an account having required permissions to run the job or as the account which created the package.
  • This method is used for decrypting secrets and key requirements of the user are satisfied.
  • There are chances that this method could fail because the secrets are decrypted by taking the current user and current computer information which will inadvertently fail if the project or package is moved to a different computer.
• Method 2: Set the value of Package Protection Level Property to Server Storage. This stores PACKAGES on the SQL Server and lets users access this through SQL Server database roles.
• Method 3: Set the value of Package Protection Level property as “Encrypt Sensitive with Password”. The method uses a password for encrypting sensitive information. This can be added to the SQL Server Agent job to run the job effectively.
• Method 4: Package configuration can be used to save sensitive information which can LATER be stored in a SECURED folder. When the package is run, the information is obtained from this config file. If we do not want to save the secrets in the package, we can set the property of Protection Level to “DontSaveSensitive”.
• Method 5: Package templates having a good protection level can be created for long-term solutions.

The config file is used for providing inputs to the connection MANAGER about the properties used by the packages and tasks at the run-time. This is especially useful when the changes are deployed on multiple locations or servers as we do not have to worry every time package runs about configuration. Depending on the server or location, the config files are automatically picked and used.
We can store config properties in multiple ways. Some of them are:

• XML configuration file: Data can be stored as an XML file.
• Environment VARIABLES: The config data can be stores as part of environment variables.
• Registry ENTRY: The config can be stored in the registry.
• Parent package variable: The config can be stored as a variable in a package of a task.
• SQL Server: The config can be stored in a database table on the SQL Server.

This property gives US the FLEXIBILITY to log or not to log the EVENTS while RUNNING the components - tasks and packages - in SSIS.

We will be using the following three components:

• ONE OLE DB Source
• Information CONVERSION Change
• One SQL Server Goal
  OLE DB source is used to get information from the Access database source. Information conversion change is used in case of any datatype ANOMALY due to data contrast between source and destination databases. SQL Server Goal is utilized when we have the bundle run from the same machine and our database server is PUT on at the off chance. It is used for improving the SQL goal.

An SSIS breakpoint is a property that allows developers to debug and review the variable or data STATUS at any point by pausing the EXECUTION of the package in the project. The breakpoints are configured in Business Intelligence Development Studio, generally KNOWN as BIDS. The following steps are followed to apply or remove SSIS breakpoints:

• Go to the control flow interface within the BIDS section.
• Right-click on the object that we want to apply or remove the breakpoint.
• Select “Edit Breakpoint” and enable/disable the breakpoint.

Yes. This can be done by designing SQL Server Agent Job with WORK steps similar to the SSIS PACKAGE. This job fetches the dtexec order utility to execute the bundle. This created bundle can be run or REQUESTED based on the requirement or can be rescheduled based on the RECURRING requirement.

Yes. SSIS supports logging which HELPS the system in analyzing and writing log entries during any run-time events. It also supports logging of CUSTOM MESSAGES, which the developer has to manually enable. Since the SSIS deals with a variety of data sources, logging is supported in multiple formats such as logging information to text files, XML files, Windows Log, etc. The scope of the logs is packages. They are maintained at the package level which means that any task that RUNS from the package can log information to other package logs. 

There are 3 types of lookup cache modes. These are PART of Lookup TRANSFORMATION in SSIS.

• Full Cache Mode: This mode helps in querying the database before the data-flow task is executed. This constitutes a critical part of the pre-execution step of the data-flow task. In this mode, the entire data COPY is copied from the table and stored into the lookup cache in SSIS.
• Partial Cache Mode: This mode helps in querying new rows in the database from different data sources. Here, the matched ROW data is cached into the SSIS lookup cache. If the lookup cache is full, the data is removed automatically from the cache based on the usage statistics of the existing rows to free up space for newly matched rows.
• No Cache Mode: Here, the data is not cached unless 2 subsequent sources come up with the same matched rows. Database queries are run to get matched data every time from the source.

Data profiling is the PROCESS of examining INFORMATION by comprehending its condition, readability, examples, NULLS, or NUMBERS. This is PERFORMED at the beginning of the advancement cycle for structuring the database blueprint and assessing the quality of the data.

The following containers are present in SSIS:

• Task Host Containers: Default container where all single tasks are available and are used for background scenes in SSIS. This is not present in the toolbox of Visual Studio and is assigned to the task implicitly. This container is also used to extend event handlers and variables.
• Sequence Containers: These handle the flow of package subset and helps to divide packages into smaller pieces.
  • These are displayed in the Control Flow tab. We can drag and drop containers from the toolbox in the design pane and then add a set of tasks into the container. Following things can be achieved using sequence containers:
    • Group tasks for disabling a portion of the unused package.
    • To narrow the VARIABLE scope to the container.
    • Manage properties of tasks easily by setting container properties.
    • Ensure multiple executions of tasks by making use of one method.
      • CREATE transactions for data-related tasks and not package as a whole.
    • Create event handlers such as sending mail in case things go wrong.
  • The below example shows two tasks that need to be successfully executed before calling the third script task.

• For Loop Containers: Helps to create a loop in the EXECUTION of packages. SSIS does this by initializing expression optionally and continues evaluation until expression becomes false. In the below example, We have the task name “Wait for file to arrive” which is INSIDE For Loop Container, and the task is executed until the expression is evaluated to false. The Load File task is executed after the loop execution is complete.

• Foreach Containers: This comprises of powerful loop mechanism which enables the loop using object collection. As the collection is looped, the variable is assigned values from the collection which is used by connections or tasks outside or within the container.

Merge Transformation: This does the task of merging data from 2 sources to single OUTPUT. This is useful when we NEED to break the data flow path to a separate error path and once the error is handled, merge that back to the main data flow. To apply this transformation, the data should be sorted first and the metadata information for the 2 paths should be the same. The sorting can be done by applying sort transformation before the merge or by providing an ORDER BY clause in the source connection.

Union All Transformation: Union all transformation working is SIMILAR to merge transformation but the only difference is that this does not require sorted data. Data from different sources are transformed and COMBINED into a single output.

Data flow is the data transferring process from the source to a specified donation. It defines the way how data or information is SENT from one point to other. It consists of 3 main components:

SSIS GIVES control to the users to perform the data transformation process from source to destination utilizing various built-in transformation components. The components provided by the SSIS HELPS users in data cleaning and manipulation in the pipeline. For achieving this, the user needs to attach a data flow task to the PACKAGE control flow which is later executed within the package by creating, ordering, and running the data flow. It is WORTHY to note that each data flow task has a separate data flow engine instance.

Deployment is nothing but a PROCESS of updating the state of the package from development mode to executable mode. Deployment in SSIS can be done by simply performing the right-click operation on the “INTEGRATION Services Project” and clicking on the BUILD option. This would create a “package.dtsx” file inside the bin folder named “projectbin”. The utility HELPS in deploying packages at SQL server or as an executable file on a SPECIFIED location.

Following are the steps followed to create a deployment utility:

• Go to Project and right-click on it.
• Double click on the properties.
• Choose the path location for deployment by selecting the box next to “True” under Create Deployment Utility.
• Save changes. Close this window.
• Right-click again on the project and select the build option. This creates a deployment folder in the project’s root within the BIN folder.
• The deployment folder will have a .manifest file.
• Double-clicking on the .manifest file and selecting the deploy option will perform package deployment on the SQL server.