Explore topic-wise InterviewSolutions in .

During the process of TRANSFERRING millions of records from one SOURCE to a DESTINATION, the probability of errors always exists. It is very important to identify the records that are errored out and ensure the transmission of records is smooth with no errors to the destination. To identify the errors, the errors need to be logged. The components of SSIS - source, TRANSFORMATION, and the destination - all have had support to log errors to output by means of secondary pipe by providing means to define behaviors while errors occur.

The secondary pipe or the error flow can be connected to another transformation or a destination component depending on the requirement. The error log can CONTAIN details regarding the error columns, error descriptions, and error codes.

There are some disadvantages while using SSIS. They are as follows:

• If we need to see the report of package execution, then we will have to install new software called MANAGEMENT Studio. SSIS does not have the option to publish to reporting services.
• SSIS utilizes high memory which can cause conflicts with memory utilization of SQL. This causes problems if there are requirements to run multiple PACKAGES in parallel.
• SSIS also uses high CPU resources which again results in problems while running multiple packages parallely. PROPER CPU allocation needs to be ENSURED between SQL and SSIS to avoid either of them running SLOW.

PROCESS bytes represent the amount of MEMORY currently used by the Integration SERVICES. This memory cannot be shared by other processes.

These are the TRANSFORMATIONS that are similar to the IF-ELSE condition of programming languages which checks for the condition and executes the process if the condition EVALUATES to true. Here, we can also SPECIFY the ORDER of evaluation for the conditions and also provide the DEFAULT value as the output.

SSIS has more advantages when dealing with complex transformation processes involving slow-changing dimensions. They are:

• SSIS is known for managing memory efficiently thereby RESULTING in much bigger improvements in performance when compared to stored procedures.
• SSIS supports GUI (Graphical User Interface) and HELPS developers develop complex transformations easily and in a RELIABLE way.]
• SSIS helps in easier interaction with external sources which are particularly useful during data cleansing.

If the ETL (Extract, Transform, Load) PROCESS consists majorly of Extract and Load with very LITTLE Transform, then Stored Procedures work well because they do not deal with cursors.

Connection Managers are used for gathering data from various sources and SENDING it to a destination. It facilitates system connection by INCLUDING information regarding server, data SOURCE, authentication details, database, etc.

Precedence Constraint is USED for enabling the developers to DEFINE a set of TASKS logically and define the order of execution. These are connectors used for CONNECTING all the tasks.

SSIS checkpoint is a property that PROVIDES a POINT of restart from the failure point. When the checkpoint property is set to true, a checkpoint file is created which consists of all the relevant information required to run the package from the point of failure. If the package is run successfully, the file is DELETED and is RECREATED again when the package runs. This is particularly useful as it AVOIDS re-running the whole project when the execution fails.

SSIS PERMITS the creation of TWO variable types- global and task-SPECIFIC variables. The scope of global variables is available for all TASKS of a specified process. The task-specific variables have scope specific to the task.

SSIS containers are defined as a set of logically linked tasks that ALLOWS the management of the TASK SCOPE effectively. Containers ALLOW looping through tasks set until tasks are grouped logically or until specified criteria are met. Nesting of containers is ALSO allowed and the containers are set in the Package designer section in the Control flow tab.

SSIS catalog is the database where all DEPLOYED packages are stored securely. This helps to CONTROL and HANDLE the deployed packages effectively. The users can DEPLOY their packages too. All the deployed packages are stored in the catalog as a centralized database. 

Data transformation is the process of extracting required data from a data source and is the most critical SSIS step. Post extraction, the process aids in managing and transferring the data to a specific FILE destination. There are several RULES IMPLEMENTED by this process for loading the extracted data to the destination TARGET file. Based on this, the transformations are classified as:

The manifest file is the utility having information that is useful for deploying packages USING file system WIZARD and database of SQL SERVER.

SSIS Expressions are USED for filtering information based on parameters and conditions to get desired information. They MAKE lives easy by helping to work with conditions, loops, and dynamic connections. The expression could consist of symbols, literals, identifiers, operators, and FUNCTIONS. They are mainly used for updating properties dynamically at runtime. Once we have the expressions ready, an expression evaluator parses this expression for VALIDATING the rules of the expression.
The expressions are used for the below 2 cases:

• Control Flow:
  • Here, the VARIABLE values are derived using other variables employing expressions.
  • For validating any condition, precedence constraints are used.
  • Used for setting properties dynamically at runtime by tasks and containers.
• Data Flow:
  • Expressions are used on rows to get conditional split transformations evaluating to true or false.
  • Helps to create a new column based on the result of applying expressions to get derived transformations on new columns.

SSIS packages are ORGANIZED collections of various connections and ELEMENTS related to data flow, event handlers, variables, control flow, configurations, or PARAMETERS that would be used for ASSEMBLING and programmatically building graphical design tools. They can also be used for populating data from different sources which can be later used for standardizing and administrative PURPOSES. They are created in BIDS - Business Intelligent Development Studio.

Tasks are units of work and are present in SSIS packages. We have different TASK types depending on the type of work that NEEDS to be performed. Below are some of the most COMMONLY used tasks present in SSIS:

• Execute SQLTask: Used for executing SQL statements in relational databases.
• Data Flow Task: Used for reading data from one or more data sources, transforming them, and loading it to various destinations as required.
• Analysis Services Processing Task: Used for processing objects of the tabular model or SSAS (SQL Server Analysis Services) cubes.
• FTP Tasks: Used for sending and receiving files from FTP Servers to our local folders.
• Script Task: Used for writing .Net code as per requirements.
• Send Mail Task: Used for sending emails for notifying users regarding status, progress, or outcomes of tasks.
• Execute Package Task: Used for calling different packages of the same project. It also has the feature of passing the value of variables to the package.
• Execute Process Task: Used for running application as a whole or as a batch script. It can also be used for opening software like Microsoft Word, Excel, etc, and for unzipping compressed files.
• File System Task: Used for performing file system manipulations like move files, rename them, delete them, change the location, etc.
• Custom Task: If all the existing tasks do not MEET our requirements, then SSIS provides flexibility to create NEW custom tasks. 

SSIS stands for SQL Server Integration Services. It is a part of the Microsoft SQL server and is a well-known integrated platform for performing enterprise-level data migration and transformation activities. The platform is USED for solving complex PROBLEMS like reading, loading, copying, or DOWNLOADING large files, mining data, extracting RELEVANT information, and managing SQL objects. It supports data sources like relational databases, XML, flat files, etc, and HELPS to load and send data to multiple destinations.