Top 10 SSIS interview questions

SSIS Interview Questions

1) What happens if we enable checkpoint and transaction together in SSIS package?

Ans: Transaction always gets priority over checkpoint to fulfill acid property.

2) What happens if we enable checkpoint and the package fails after processing few rows of the data flow task.

Ans: The package will start from the beginning of the data flow task, i.e it will try to load all the rows again. To avoid this we can add audit columns and remove all the loaded rows or ignore from source which has already been loaded.

3) How do we handle parallel processing inside data flow task?

Ans:  By setting the property – EngineThreads.

4) Which service maintains transaction in SSIS?

Ans: Microsoft Distributed Transaction Coordinator

5. How To Handle Late Arriving Dimension Or Early Arriving Facts?

Ans: Late-arriving dimensions sometimes get unavoidable ‘coz delay or error in Dimension ETL or may be due to a logic of ETL. To handle Late Arriving facts, we can create a dummy Dimension with natural/business key and keep the rest of the attributes as null or default. And as soon as the Actual dimension arrives, the dummy dimension is updated with Type 1 change. These are also known as Inferred Dimensions. 

6. What kind of variables can you create?
Ans: You can create global variables and task level variables in SSIS. For programmers, these variables are the same as global and function level variables. A global variable is available to all tasks across the entire job. Variables created in tasks are only available within that task.

7. Difference between lookup and merge join

Ans:Lookup returns the first match value but merge join returns all matches. Merge join requires two inputs but lookup requires one input with one lookup source. Merge join has one output but lookup has match and no match. There is no caching option in merge join but we can cache the lookup data in lookup transformation

Learn DAX for PowerBI and SSAS Tabular

Web API Introduction

What is Web API ?

A Web API is an application programming interface for either a web server or a web browser. It is a web development concept, usually limited to a web application's client-side (including any web frameworks being used), and thus usually does not include web server or browser implementation details such as SAPIs or APIs unless publicly accessible by a remote web application.

ASP.NET Web API

The ASP.NET Web API is an extensible framework for building HTTP based services that can be accessed in different applications on different platforms such as web, windows, mobile etc. It works more or less the same way as ASP.NET MVC web application except that it sends data as a response instead of html view. It is like a webservice or WCF service but the exception is that it only supports HTTP protocol.

Web API

ASP.NET Web API Characteristics

1. ASP.NET Web API is an ideal platform for building RESTful services.
2. ASP.NET Web API is built on top of ASP.NET and supports ASP.NET request/response pipeline
3. ASP.NET Web API maps HTTP verbs to method names.
4. ASP.NET Web API supports different formats of response data. Built-in support for JSON, XML, BSON format.
5. ASP.NET Web API can be hosted in IIS, Self-hosted or other web server that supports .NET 4.0+.
6. ASP.NET Web API framework includes new HttpClient to communicate with Web API server. HttpClient can be used in ASP.MVC server side, Windows Form application, Console application or other apps.

Endpoints of Web API

Web api endpoint is the Unique URL to call a specific method or function.

Tools for testing and monitoring web api

1. Postman
2. Swagger
3. Fiddler
4. Soap UI

SQL Server Reporting Services best practices

SSRS (SQL Server Reporting Services) Best Practices

Data Sources

1.     Use shared data sources even if there is only dataset

2.     In case of providing fixed credentials, use read-only user accounts

3.     Do not create report using SSRS Report Builder

Data Sets

1.      Try to keep report fields in single dataset. If the parameters are being populated from dataset then that dataset must be kept separate.

2.      Do not hard code anything either in the query or calculated dataset fields. Use parameters instead of hardcoding values

3.      Instead of filtering data in the dataset filters, filter the same in query

4.      Do not pull unnecessary fields

5.      Do not call queries that updates data in the data source objects e.g. Database Table

6.      Avoid usage of shared datasets

7.      Avoid usage of VB code i.e. report code

8.      If report is based on database then use stored procedure or views

Parameters

1.     Use appropriate data types

2.     Use report code or dataset queries to populate dynamic and custom parameters

3.     Provide default values to parameters

4.     Configure the default value of parameters in deployed report from configuration option without actually editing the RDL

5.     If users are not supposed to update parameter values then use internal parameters instead of keeping the parameters hidden

Report code

1.     Use modular approach (Create separate function even for small requirement)

2.     Do not hard code

3.     Commenting is must for every function

Expressions

1.     Do not use ReportItems!Fields as it might get changed and also it is difficult to maintain over the time

2.     Use ReportItems!Fields only for cell formatting for that particular cell

Report Area

1.     If report shows same static values at multiple places, then create parameter of the same and use it everywhere

2.     Remove unwanted white space from the report

Performance and optimizations

1.     Do not pull unnecessary data

2.     If possible then perform aggregations at the query level and only rendering related customizations at the report level

3.     If the source of the report does not change frequently, then use SSRS caching feature and also set the processing options accordingly

4.     If you use sub reports/ drill down reports then in child reports, do not add logic of populating parameters which are consuming values of parent report

5.     Try to use SSRS parameter sniffing

SSRS Coding Standard

Naming conventions

1.     Data sources

a.      Follow [server].[database/ cube] naming convention

2.     Data sets

a.      Provide meaningful names to datasets

b.     Prefix the names with 'ds'

c.      Give proper names to dataset fields

3.     Parameters

a.      Provide meaningful names to datasets

In the prompt field, put proper phrase or text that you want to show in parameter

SSIS (SQL Server Integration Service) Naming Conventions

SSIS (SQL Server Integration Service) Naming Conventions

Task name	Prefix	Type	New
For Loop Container	FLC	Container
Foreach Loop Container	FELC	Container
Sequence Container	SEQC	Container
ActiveX Script	AXS	Task
Analysis Services Execute DDL Task	ASE	Task
Analysis Services Processing Task	ASP	Task
Azure Blob Download Task	ADT	Task	*
Azure Blob Upload Task	AUT	Task	*
Azure HDInsight Create Cluster Task	ACCT	Task	*
Azure HDInsight Delete Cluster Task	ACDT	Task	*
Azure HDInsight Hive Task	AHT	Task	*
Azure HDInsight Pig Task	APT	Task	*
Back Up Database Task	BACKUP	Task	*
Bulk Insert Task	BLK	Task
CDC Control Task	CDC	Task	*
Check Database Integrity Task	CHECKDB	Task	*
Data Flow Task	DFT	Task
Data Mining Query Task	DMQ	Task
Data Profiling Task	DPT	Task	*
Execute Package Task	EPT	Task
Execute Process Task	EPR	Task
Execute SQL Server Agent Job Task	AGENT	Task	*
Execute SQL Task	SQL	Task
Execute T-SQL Statement Task	TSQL	Task	*
Expression Task	EXPR	Task
File System Task	FSYS	Task
FTP Task	FTP	Task
Hadoop File System Task	HFSYS	Task	*
Hadoop Hive Task	HIVE	Task	*
Hadoop Pig Task	PIG	Task	*
History Cleanup Task	HISTCT	Task	*
Maintenance Cleanup Task	MAINCT	Task	*
Message Queue Task	MSMQ	Task
Notify Operator Task	NOT	Task	*
Rebuild Index Task	REBIT	Task	*
Reorganize Index Task	REOIT	Task	*
Script Task	SCR	Task
Send Mail Task	SMT	Task
Shrink Database Task	SHRINKDB	Task	*
Transfer Database Task	TDB	Task
Transfer Error Messages Task	TEM	Task
Transfer Jobs Task	TJT	Task
Transfer Logins Task	TLT	Task
Transfer Master Stored Procedures Task	TSP	Task
Transfer SQL Server Objects Task	TSO	Task
Update Statistics Task	STAT	Task	*
Web Service Task	WST	Task
WMI Data Reader Task	WMID	Task
WMI Event Watcher Task	WMIE	Task
XML Task	XML	Task
Transformation name	Prefix	Type	New
ADO NET Source	ADO_SRC	Source	*
Azure Blob Source	AB_SRC	Source	*
CDC Source	CDC_SRC	Source	*
DataReader Source	DR_SRC	Source
Excel Source	EX_SRC	Source
Flat File Source	FF_SRC	Source
HDFS File Source	HDFS_SRC	Source	*
OData Source	ODATA_SRC	Source	*
ODBC Source	ODBC_SRC	Source	*
OLE DB Source	OLE_SRC	Source
Raw File Source	RF_SRC	Source
SharePoint List Source	SPL_SRC	Source
XML Source	XML_SRC	Source
Aggregate	AGG	Transformation
Audit	AUD	Transformation
Balanced Data Distributor	BDD	Transformation	*
Cache Transform	CCH	Transformation	*
CDC Splitter	CDCS	Transformation	*
Character Map	CHM	Transformation
Conditional Split	CSPL	Transformation
Copy Column	CPYC	Transformation
Data Conversion	DCNV	Transformation
Data Mining Query	DMQ	Transformation
Derived Column	DER	Transformation
DQS Cleansing	DQSC	Transformation	*
Export Column	EXPC	Transformation
Fuzzy Grouping	FZG	Transformation
Fuzzy Lookup	FZL	Transformation
Import Column	IMPC	Transformation
Lookup	LKP	Transformation
Merge	MRG	Transformation
Merge Join	MRGJ	Transformation
Multicast	MLT	Transformation
OLE DB Command	CMD	Transformation
Percentage Sampling	PSMP	Transformation
Pivot	PVT	Transformation
Row Count	CNT	Transformation
Row Sampling	RSMP	Transformation
Script Component	SCR	Transformation
Slowly Changing Dimension	SCD	Transformation
Sort	SRT	Transformation
Term Extraction	TEX	Transformation
Term Lookup	TEL	Transformation
Union All	ALL	Transformation
Unpivot	UPVT	Transformation
ADO NET Destination	ADO_DST	Destination	*
Azure Blob Destination	AB_DST	Destination	*
Data Mining Model Training	DMMT_DST	Destination
Data Streaming Destination	DS_DST	Destination	*
DataReaderDest	DR_DST	Destination
Dimension Processing	DP_DST	Destination
Excel Destination	EX_DST	Destination
Flat File Destination	FF_DST	Destination
HDFS File Destination	HDFS_DST	Destination	*
ODBC Destination	ODBC_DST	Destination	*
OLE DB Destination	OLE_DST	Destination
Partition Processing	PP_DST	Destination
Raw File Destination	RF_DST	Destination
Recordset Destination	RS_DST	Destination
SharePoint List Destination	SPL_DST	Destination
SQL Server Compact Destination	SSC_DST	Destination	*
SQL Server Destination	SS_DST	Destination