Data Engineering Technical Blog

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 Ar...

Learn DAX for PowerBI and SSAS Tabular

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 ASP.NET Web API is an ideal platform for building RESTful services. ASP.NET Web API is built on top of ASP.NET and su...

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.    ...

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 T...