SSIS Interview Question and Answers Part 1

1.In BIDS, the SSIS project contain 10 packages. But How to deploy only 5 packages in Destination machine even though Manifest file contains all 10 packages after the Build?
-Open the Manifest file in any editor like BIDS or Notepad, keep the required 5 packages, remove remaining 5 packages.
-Save and Close the Manifest file.
-Double click on Manifest file to deploy the required 5 package.

2.What are the different types of Transformations you have worked

AGGEGATE -The Aggregate transformation applies aggregate functions to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.
The Aggregate Transformation supports following operations:
Group By, Sum, Average, Count, Count Distinct, Minimum, Maximum

AUDIT - Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc..

CHARACTER MAP - When it comes to string formatting in SSIS, Character Map transformation is very useful, used to convert data lower case, upper case.

CONDITIONAL SPLIT – used to split the input source data based on condition.

COPY COLUMN - Add a copy of column to the output, we can later transform the copy keeping the original for auditing.

DATA CONVERSION - Converts columns data types from one to another type. It stands for Explicit Column Conversion.

DATA MINING QUERY – Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.

DERIVED COLUMN - Create a new (computed) column from given expressions.

EXPORT COLUMN – Used to export a Image specific column from the database to a flat file.

FUZZY GROUPING – Groups the rows in the dataset that contain similar values.

FUZZY LOOKUP - Used for Pattern Matching and Ranking based on fuzzy logic.

IMPORT COLUMN - Reads image specific column from database onto a flat file.

LOOKUP - Performs the lookup (searching) of a given reference object set against a data source. It is used to find exact matches only.

MERGE - Merges two sorted data sets of same column structure into a single output.

MERGE JOIN - Merges two sorted data sets into a single dataset using a join.

MULTI CAST - is used to create/distribute exact copies of the source dataset to one or more destination datasets.

ROW COUNT - Stores the resulting row count from the data flow / transformation into a variable.

ROW SAMPLING - Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.

UNION ALL - Merge multiple data sets into a single dataset.

PIVOT – Used for Normalization of data sources to reduce analomolies by converting rows into columns

UNPIVOT – Used for demoralizing the data structure by converts columns into rows incase of building Data Warehouses.

3. What are the different types of Transaction Options

Required: If a transaction already exists at the upper level, the current executable will join the transaction. If No transaction at the upper level, a new transaction is created automatically.

Supported:In any executable, if there is a transaction at upper level, the executable join the transaction else do not create a new transaction.

Not Supported:The executable of the package do not honour any transaction ie do not join other transaction nor creates new transaction.

4. Explain about Checkpoints with properties

Checkpoint is used to restart the package execution from the point of failure rather than from initial start.

Set the following Properties:
   CheckpointFileName: Specifies the name of the checkpoint file.
   CheckpointUsage: Never, IfExists, Always
   SaveCheckpoints: indicates whether the package needs to save checkpoints.  This property must be set to True to restart a package from a point of failure.

FailPackageOnFailure: property needs to be set to True for enabling the task in the checkpoint.
Checkpoint mechanism uses a Text File to mark the point of package failure.

These checkpoint files are automatically created at a given location upon the package failure and automatically deleted once the package ends up with success.

5. Package configuration? Different types of Configuration Files

The package can be transferred across various environments like development and unit testing, system testing, UAT and production. Most packages will have environment specific variables like connection string to a database or path to a flat file, or user defined variables etc. that would be impacted while moving the package across environments as part of deployment process. Hence, it is mandatory to change these environment dependent variables when the package is transferred across environments. Package configurations help in managing such changes without actually opening and editing the SSIS package in Business Intelligence Development Studio (BIDS). After deploying the package to a different machine (using SQL Server or file system deployment mode) it is mandatory to copy the related package configuration files on to that machine. If the package is scheduled to run through a SQL Agent job, the configuration file should be added while creating the job so that package will read the information from the configuration file. While executing the job, SQL Agent will take the design time values for connection strings if the package configuration file is not supplied.

There are 5 configuration types available with package configurations.