SISS interview questions

SISS interview questions

SISS is a field that many companies are looking forward to hiring eligible professionals. It is called the SQL Server Integration Services. This is a version of Microsoft's SQL Server. This is a plus point for candidates who are already familiar with the SQL and SQL Server. Several organizations are looking for both freshers and experienced personnel to handle this position. So, we have gathered some of the most common SISS interview questions to guide candidates. It is important to understand the use of SISS before we go into the article. SISS is used for migration of data and also for data transformation. A business can frame workflow applications and implement data integration in a big way. With SISS and its tools, data extraction, loading, and transformation is made simple.

Also, it is easy to install and learn about the different data warehouse tools it offers. A number of graphical tools, debugging packages and wizard building platforms are also available. We can develop simple ETL ( Extraction, Transformation, and Loading) packages to assist a project. In this article, we will explore many such topics that are important in a SISS interview. This will give an idea for database managers to answer both technical and conceptual parts of the interview.

Download SISS interview questions PDF

Below are the list of Best SISS interview questions and Answers

The components in SISS include the data flow, control flow, package explorer and event handler. All of these components come in the package after installing the SISS application from Microsoft. Other important components include runtime engine, flow pipeline engine, SISS window service, and SISS object model. We use a runtime engine to manage the workflow of the SISS package. The flow pipeline engine helps to transform the source data to its respective destination. It also helps with the in-memory transformations.

The SISS package is available for installation online and it is an organized set of connections. These connections include event handlers, data flow elements, control events, variables, parameters, and other configurations. SISS provides both graphical tools and programming tools. So, these can be assembled using either of the tools.

Containers are helpful in grouping different tasks together. Each one of them has a purpose that helps in performing various tasks. Here is a list of containers.

  • Loop containers

When we have to execute a particular task for a fixed number of times, this container is used. If we need to start a loop for 5 times, then we can specify the number while using this container. This saves time as we need not repeatedly do the same task for 5 times. We also need not declare 5 different packages to complete the task.

  • For each loop container

If the number of times a task is to be executed and we are unaware of the exact number, then this container is used. When we execute such a task, each and every folder is selected. Then the task takes place one by one. As soon as it is complete, we are informed that the task is complete.

  • Sequence containers

These are used when we have to group two or more similar tasks. It is a simple container in the SISS package.

  • Task host containers

A task host container is a default container in SISS. This is not found in the toolbox as does not need any implementation. This automatically gets assigned to each task that is performed.

In SISS, we define a task to be the work that needs to be completed for a particular command. This is very similar to the tasks in programming languages. In these languages, a task is done when code is given as input. Similarly, there are two types of tasks in SISS and they are database maintenance task, control flow task.

MergeUnion all
Data that is stored in two different paths can be merged into one using the Merge optionUnion all is also used to merge two or more data from different paths into one
Before executing, sorting transformation should be applied. This helps to sort the data before mergingSorting transformation is not required for union all functions
The metadata of this type should be the same for all dataIn union, all option, all data from different sources are transformed and then is displayed as a single set.

In SISS, there four types of data viewers. We can display data in the form of histograms, scatter plots, grids and column charts. This is an advantage because of it easier to understand than tables.

After we install the SISS package, it can be saved on locations including Package Storage, File System, and SQL Server.

In any coding language or back-end application, a task is executed only after satisfying a set of parameters. Only then the task is assigned on a path that is suitable to complete the given command. In SISS too there are parameters that define the path of a task. A precedence constraint is a task sequence that is defined by a logical statement or a command. It is in this sequence a task is being performed. We can also use a connector to connect these tasks and precedence constraints.

  • Completion

It is mandatory for the preceding container to get executed completely. The result may be a success or a failure. Only when it is completed, the current workflow starts preceding.

  • Success

If the preceding container has completed execution and is also successful, then it is represented by a green line.

  • Failure

When the preceding container is executed but failed then it is marked with a red line.

It is very important to analyze the source data before proceeding with tasks. In SISS, we can analyze and organize the given data. This is called data profiling. There is a list of steps followed in data profiling. It includes identifying and cleaning the data, finding the number of true value and null values, etc. This gives a better understanding of the source data and is helpful in creating the database destination design schema. This is usually done at the beginning of the project. This gives an idea on how to proceed with the project and how long will it take to complete.

After installing the SISS package, we convert them into an executable package that can be used to implement SISS functions. This process is called deployment of SISS package. Additionally, there is a deployment utility that allows us to deploy the package from any location.

  • To deploy we can use the following steps.
  • Right-click on the project integration and then select their properties.
  • Here click 'True' and create the deployment utility option. Once the changes are made, we can close this window and right-click on the project to build it.
  • We can open the bin folder, the deployment folder is created. This is located in the main project location that was created earlier. Then double click on the .manifest file to find the deploy package on the SQL server.

 

 

There are a number of connections that can be used on SISS. This includes XML, Excel, .netSQL client, flat file, ODBC and OLEDB.

As SISS is a database server, variables are highly essential. It is used to store the value of a particular data. Only when the variable and data type is assigned, the input data is valid to get stored. There are two types of variables in SISS which are system variable and user variable.

A breakpoint is used during the development of the SISS package. Sometimes it is also used when troubleshooting in the SISS package. If we wish to pause the execution process during development, then breakpoint is used.

A checkpoint is used when a project fails. This feature restarts the project all over again. While executing, checkpoint keeps saving information about the execution package. It gets deleted if the project is successful otherwise it repeats the project again.

Execute package task is used to create a parent package which is later used to develop child packages. As the package keeps growing, there is a lot of advantages while creating child packages. Configuration properties are present in the executive package which makes it reliable. We can also separate the workflows and make development shorter than before. Testing of the project is made simple too.

Each and every task in the SISS package has a logging mode. This is a property that is exclusive for every task. The values accepted for this property will include enabled, disabled and UseParentSetting.

  • Enabled, as the name suggests is used to enable the component or task.
  • Disabled will disable the components.
  • UseParentSetting is used to modify or optimize the parent setting.
  • Logging, on the other hand, is done for events like onWarning and onError. This can be used for any type of file that is used on SISS.

There are two types of flow in SISS, they are data flow and control flow.

  • Control flow is a great feature in SISS that can link two or more tasks together. Linking is done both logically and graphically. Later these are programmed graphically only. There three common connectors which are a success, failure and complete. For other complex control flow integrations, FX (an expression) is used.
  • Data flow is executed along with the data flow engine that helps in transformation. When this is added in the package, we can use the Edit, Transform and Load (ETL) function.

 

 

In SISS, there are a lot of sub-transformations. The main types include business intelligence transformations, row transformations, row-set transformations, and split transformations. A number of custom transformations can also be used. These transformations come with in-memory commands so that we need not use any SQL scripts. Other essential transformations in SISS include synchronous and asynchronous transformations.

When we need to modify the project, data source views, data sources, and other files, we can use solution explorer. All that we perform on SISS is stored here that can be accessed for modification.

A package is an object that is used to implement the ETL functionality. Whereas a project is a container that is used to develop the SISS packages.

If we use the ignore failure option in a transformation, the error in it is ignored. It then proceeds to the next transformation. This is helpful because we skip the need for failing a package. Instead, the error is redirected to some other transformation. This is used when the project is to be completed soon.