MSBI Real Time Training Designed for Aspiring:
  • MSBI Developers
  • MSBI Leads
  • MSBI Testers
  • MSBI Consultants
  • MSBI Designer / Modeler / Architects

Call US : 040-66638869



Batch Schedules:
Schedule Mode Duration Fee Link
18-02-2019 8AM IST Class Room 55 Hr 10,000 Register
18-02-2019 7AM IST Online 55 Hr 15,000 Register
16-02-2019 10AM IST Week end 55 Hr 15,000 Register
If Schedule doesn’t suit you, for:
  • SEPARATE ONLINE /
  • WEEK-END /
  • ONE ON ONE /
  • CUSTOMIZED TRAINING AVAILABLE

Call US : 09573168449




SSIS Interview Questions ( Simple, Medium and Complex)

Datawarehouse Fundamental Questions
  • IT applications and differences between OLTP and OLAP?
  • 1) OLTP (Online Transaction Processing)
  • Purpose: Day-Day business storage
  • Process: Front-End, Back-End process
  • Points:
  • a) Data: Less data
  • b) Model: Noramlized model (more tables,smaller tables)
  • c) Joins: More joins (So retrieval it is slower)
  • d) Access Frequency: High [Milliseconds to seconds]
  • e) Volatality: Volatile [Read, Ins, update, Delete]

  • 2) OLAP(Online Analytical Processing)
  • Purpose: For analysis and mining
  • Process: Layered architecture
  • Points:
  • a) Data: More data
  • b) Model: DeNoramlized model (Less tables, complex tables)
  • c) Joins: Less joins (So retrieval it is faster)
  • d) Access Frequency: Low [Day/week/month...]
  • e) Volatality: NonVolatile [Read]

  • What is Datawarehouse [Single line] and warehouses available in the market?
  • Hige storage area and which is suitable for decision making.
  • Ex: Teradata,Oracle,Sqlserver,Sybase,Netezza, GreenPlum etc...

  • Difference between OLAP and BI?
  • BI is a process which uses OLAP approach to gather, convert and present the data.

  • Difference between Analysis and Mining?
  • Analysis talks about 'current understanding of data' and 'past analysis of data', where as mining talks about 'future prediction of data'.
  • Note: Datamining is a knowledge analysis and discovery method.

  • How many types of decision making available in IT?
  • a) Strategic-- Past analysis [OLAP data]
  • b)Tactical--Current and recent [OLTP]

  • Difference between OLTP and ODS?
  • OLTP holds realtime data where as ODS holds near real time data[daily 4 times from OLTP]
  • ODS helps in two ways >
  • a) Input to DWH
  • b) Incase enterprise database [OLTP] fails, it acts like enterprise db and continue operations.
  • Note: All top companies use ODS in the ETL flow.
  • OLTP similar terminologies?
  • a)Operational Systems
  • b)Enterprises databases
  • c)Business Processing Systems
  • d) Transactional systems

  • Can you describe an End-End typical ETL Flow?
  • OLTP->ODS->Stage Area[Files]->DWH[Stage]-->DWH[WorkArea]->DWH[Target]

  • How many life cycle does a DWH project need?
  • Three life cycles a)ETL b) Semantic c) Reporting
  • Famous DWH life cycles: Agile, iterative incremental [Block approach], V model

  • Explain Inmon Characterstics/ Principles?
  • Inmon Characterstics:
  • a) Subject oriented: Store data based on operation but not application.
  • Ex: Savings coount subject area
  • Application2 : a)Withdraw b)deposit
  • Subject Area: withdraw+deposit single area
  • b) Integrated: Integrate from diverse applications by eliminating inconsistencies and following standards.
  • c) Nonvolatile: No change of data. [Don't modify the data] Helps to maintain history.
  • d) Timevarient: Data store base on timeframes [granularity load / grain]
  • We can perform the below operations easily.
  • a) Current understanding
  • b)Past analysis
  • c)Future predictions

  • What is granularity and which grain your project has?
  • Granularity talks about the level of data maintaining in the project. Dwh has multiple tables with multiple grains.
  • Always the lowest grain has lot of burden but improves analytical performance.
  • Ex: Day/ week/month etc.. grains Day grain is recommended for better analysis

  • What is datamart and how many types available?
  • DataMart--Data sub store for specify business/ operation / functioality May or not not be a subset of DWH.
  • Three types of datamarts
  • a)Dependent-- DWH created first and then datamart [subset of DWH]
  • Ex: ICICI DWH, Savings account dependent datamart
  • b) Independent-- Directly created from source systems [we don't use DWH here]
  • Ex: ICICI employee payroll System [No history, not much analysis required]
  • c) Logical Datamart--It is replica of another data mart

  • How do we create datamarts practically?
  • a) By using Complex Views [Materialized views] --Dependent DM
  • b) By using a complex table --Depedent DM
  • c) By using a seperate physical storage area --Independent DM

  • How many types of DWH approaches available, which approach your company following?
  • Two approaches
  • a) KIMBal approach--Datamarts-> DWH [Bottom up approach]
  • b) Inmon approach-- DWH -->Datamarts [Top down approach]
  • My company using KImbal approach for DWH implementation because of dynamic decisions and adhod requests.

  • How many types OLAP available, which is effective?
  • OLAP--Online Analytical Processing
  • It creates aggregates(sum,avg, max,min stddev,covarience etc...) for decision making.
  • ROLAP: Relational OLAP--Data and aggrgates in the relation are [OLTP area]
  • Latency time less [fresh data anlysis]
  • Slower analysis
  • Minimal set up
  • Ex: Smal, Medium
  • MLOAP: Multidimensional OLAP-- Data and aggregates in mutidimensional area[ Cube]
  • Adv: More and detailed analysis this is helpful
  • Latency time is high [ fresh data analysis is not possible]
  • Complex set up
  • Ex: Corporates
  • HOLAP:Data in relational area and aggreagates in multidimensional area [cube]
  • Ex: Latency time avg
  • Avg set
  • Desktop OLAP: Here the data and aggregates on PC based desk top applications
  • Ex: Excel, LOtus, FoxPro,VISICALC etc...
  • Explain Time hierarchy?
  • Hour-->Day-->Week-->Fort Night-->Month-->Quarter-->half Year(Semister)-->Year

  • You have two databases with same size, then dow do you identify which is OLTP or OLAP?
  • Normalized model is OLTP, Denormalized model is OLAP

  • Need of Data warehouse?
  • 1)To Analysis of data and history maintenance.
  • 2)Companies require Strategic information to face the competition in market.
  • 3)The Operation system are not designed for strategic information.
  • 4)To maintain history of data for whole Organization and to have a single place where the entire data stored.

  • What is data warehousing and Explain Approaches?
  • Many companies follow either characteristic defined by W.H.Inmon or Sean kelly.
  • a)Inmon definition
  • Subjected Oriented, Integrated, Non Volatile,Time Variant
  • b)Sean Kelly definition
  • Seperate, Available, Integrated,TimeStamped,Suject Oriented, Non Volatile, Accessible
  • c)Dwh Approaches
  • There are two Approches
  • 1.Top Down by Inmon
  • 2.Bottom Up by Ralph kimbal
  • Inmon approach:Enterprise datawarehouse structured first and next Datamart created.(TopDown).
  • Ralph kimbal:Datamart designed first, later Datamarts to Datawarehouse designed.(BottomUp).

  • What are the responsibilities of a data warehouse consultant/professional?
  • The basic responsibility of a data warehouse consultant is to ‘publish the right data’. Some of the other responsibilities of a data warehouse consultant are:
  • 1. Understand the end users by their business area, job responsibilities, and computer tolerance.
  • 2. Find out the decisions the end users want to make with the help of the data warehouse.
  • 3. Identify the ‘best’ users who will make effective decisions using the data warehouse
  • 4. Find the potential new users and make them aware of the data warehouse.
  • 5. Determining the grain of the data.
  • 6. Make the end user screens and applications much simpler and more template driven.

  • What are fundamental stages of Data Warehousing?
  • a)Offline Operational Databases - Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system's performance.
  • b)Offline Data Warehouse - Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data structure.
  • c)Real Time Data Warehouse - Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (e.g an order or a delivery or a booking etc.)
  • d)Integrated Data Warehouse - Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

  • What is Datamart Explain Types?
  • It is a specific Subject area or Functionality or Task. It is Designed to facilitate end user Analysis.
  • Wrong Answer-- It is a subset of warehouse
  • Types of Datamarts
  • Dependent, Independent, Logical.
  • Dependent: Created from Datawarehouse as a seperate physical store.
  • Independent :Created directly from operational systems to a seperate physical store.
  • Logical: Exists as a subset of existing Datawarehouse.
SSIS Interview Questions ( Simple, Medium and Complex)
  • Q: Which edition of MSBI is used by the companies?
  • Ans: Business Intelligence Edition
  • Q: What is SSIS?
  • Ans: It is Integration of three services[ETL, Windows Admin,Sqlserver Admin]
  • Q: What is the previous component of SSIS?
  • Ans: DTS[Data Tranmsformation Services]
  • Q: SSIS version differences.
  • Ans:
  • Q: How many engines does SSIS use and explain the operations?
  • Ans: Two engines SSIS use a) SSIS Runtime Engine--Starts when pkg starts
  • 2) Data Pipeline engine--Starts when DataFlow Task Starts
  • Q: What is inmemory process?
  • Ans: DataFlow opertation is Inmemory process. It retrives source data into
  • SSIS internal memory (inmemory) and perform operations (options) and loads from the memory to Target.
  • Q: What is Solution, Project and Package?
  • Ans:
  • Solution--Is a container for one or more projects
  • Project--Collection of packages and related files
  • Package--Collection of tasks and containers.
  • Q: Whare the components a package has and explain in single line?
  • Ans:
  • It has five components.
  • a) Control flow-- controls various tasks and containers with precedence constraints
  • b)Data Flow--contains sources, targets and transformations
  • c) Event Handler--Perfoms action based on event
  • d) Parameters--Similar to variables but take user interaction, helpful in catalog deployment
  • e) Package Explorer--Single view of all components inside package
  • Q: Difference between control flow and data flow?
  • Ans: COntrol flow is the brain of apckage and controls various tasks and containers.
  • It has ETL,WindowsAdmin and SqlServer Admin tasks.
  • Data flow is a component inside control flow and it is heart of ETL process.
  • It transfors data from sources to destinations by doing operations.
  • Q: Difference between Variable and Parameter?
  • Ans:
  • Parameter:
  • a)Take value and support user interaction at runtime
  • b)Scope is across package
  • Variable:
  • a) Take value and does not support user interaction
  • b) Scope at Package/ Container/ Task Level
  • c) Variables of two types 1. System Defined 2. User Defined variables
  • Q: What is the difference between System defined and User defined variables?
  • Ans:
  • System defined variables crated by the system at the time of package creation
  • Store under System namespace.
  • Generally they hold Audit information
  • No Scope
  • Ex: Execution Time,Package Name, User Name etc
  • User defined variables crated by the user.
  • Store under User namespace.
  • Generally they hold user information
  • Three scopes a)Package b)Container c) Task
  • Ex: Numeric value 10
  • String Value MSBIClass etc...
  • Q: What are the extensions of Solution, Project,Package, Paramter and connection Strings?
  • Ans:
  • Solution: .sln
  • Project: .dtProj
  • Package: .dtsx
  • Parameter: .param
  • Connection Manager: .conmgr
  • Q: When do we Miscellaneous folder in SSIS?
  • Ans: Generally Business Docs, LLDs, and other credential docs are maintained here.
  • Q: Difference between COntainer and Group?
  • Ans:
  • Group is logical object which hold tasks and containers.
  • No properties associated to group.
  • Groups are ungrouped.
  • Container is physical object which hold tasks and containers.
  • Properties associated to container.
  • Containers support only deletion.
  • Q: How do yopu identify package and task functionality easily?
  • Ans: By using naming conventions and annotations[detailed descriptions]
  • Q: How many ways we can execute package ?
  • Ans: 8 ways [Materail]
  • Q: How many colors a package uses?
  • Ans: 5 colors
  • Green--Success
  • Red--Failure
  • Orange/Yellow--Running
  • Grey--Disable
  • White--Ready to execute
  • Q: How many types of files SSIS support
  • Ans: 4 types a) Flatfile b)Excel c) XML d) Raw file
  • Note: Refer to material for explanation
  • Q: What is CSV file and how do you open it?
  • Ans: Comma Seperated Value file, we open in two ways
  • 1. Flatfile 2. Excel
  • Q: How do you use csv file in SSIS?
  • Ans: Flatfile source
  • Q: Which extension of flatfile data you get mostly?
  • Ans:
  • a) txt--text file
  • b)dat--data file
  • c) out--output file
  • d)csv--comma seperated value file
  • Q: What is delimiter and explain some dlimiters?
  • Ans: Data seperator is delimiter.
  • Ex: , | : $ & @ LF CRLF etc...
  • Q: What is CRLF?
  • Ans: Carriage return[First char of a line] line feed/ forward [New line]
  • New line first character--Enter key
  • Q: How many types of flatfiles avaulable?
  • Ans: Two types a) Fixed length b) Delimiter
  • Q: What is TextQualifier, how is it helpful?
  • Ans: Qulifies the text with some indicators.
  • Ex: 'vinay'--single quote is text qualifier
  • Q: How many settings we need to know before processing flatfile?
  • Ans: 7 settings [ refer to material]
  • Q: What is codepage, which code page is useful in realtime to process all types of data?
  • Ans: Encrypted charset is codepage,in realtime Unicode or UCS [2/4/8] suitable.
  • Q: Data loading how many ways in real time?
  • Ans:
  • a) Truncate and load
  • b) Incremental load [it is of three types 1.Direct incremental 2. SCD load 3. CDC Load]
  • Q: What is Delta Load?
  • Ans: Differentiated records load is delta load.
  • Q: What is SCD?
  • Ans: Slowly changing dimension for implementung actions for new and modified records.
  • Q: What is CDC?
  • And: Change Data Capture for taking sources changes and doing some actions. Source changes are Inserts,updates and deletes.
  • Q: Can you give alternate terminologies for source, target, import and export?
  • Ans: Source--> Data producer/provider/feed/input
  • Target-->Desintation/ Data consumer/ Data output
  • Import-->Loading/Populating
  • Export-->Unloading/unpopulating
  • Q: Can yopu explain me Dataflow task features briefly?
  • Ans: Moves data from source to destination.
  • Uses internal buffer while processing [in memory process]
  • Each data flow task initiates one Execution Tree internally.
  • Q: Why DataFlow task runs faster than all other tasks in SSSI?
  • Ans: Seperate process [in memory process] and seperate engine.
  • Q: What type of default datatypes dows a Flatfile and Excel take?
  • Ans: Flatfile take : DT_STR Excel ake:DT_Wstr
  • Q: What is FastParse option in flatfile source?
  • Ans: Ensures faster compilation or validation incase of date and numeric columns.
  • If we don't mention this option at flatfile source, but you specified to one of the column as DT_I4, then every value validated for numeric evalution.
  • Q: What is the diff between External and Output cols?
  • Ans: External cols are source cols, where the propertie should not be modified. Output cols are columns passed to the next component,where the properties could be modified.
  • Q: Why SQL command option is helpful to process source data?
  • Ans: We will get required columns and rows from single or multiple tables. Reduces network traffic, burden and load.
  • Q: How do you identity Buffer Size?
  • Ans:
  • Row Size * No of rows
  • Row Size: PID(INT), PNM(Char(30)),PLOC(char(30)): 4 + 30+ 30=64 Bytes
  • 10000 rows: 10000 * 64 --Buffer Size
  • Q: Which language does SSIS support?
  • Ans: It has its own language, i.e Integration Services Language
  • Q: Difference between DT_Dtr,DT_Wstr?
  • Ans: Dt_Str is ASCII based so code page required to convert where as DT_Wstr is unicode based so no code page is required.
  • Q: What is transform and how many types available?
  • Ans: Intermediate operation between components is called transformation.
  • [Ex: Sort, Merge, Aggregate etc...]
  • The component which implements this is transform.
  • Two types of transforms a) Synchronous b) Asynchronous
  • Q: Differences between Synchronous and Asynchronous transforms?
  • Ans:
  • Synchronous:
  • a) Source and target record counts mostly match
  • b) Transform is non blocking [blocking of memory not happened]
  • c) No seperate execution thread in the execution tree
  • Ex: Data conversion, Derived column, Oledb command
  • ASynchronous:
  • a) Source and target record counts mostly unmatched
  • b) Transform is blocking [blocking of memory happened]
  • Blocking is of two types a)Partial blocking b) Full blocking
  • c) Seperate execution thread in the execution tree
  • Ex: Sort, Merge, Union, Union All, Aggregate etc...
  • Q: Give me examples for Full and Partial Blocking Transforms?
  • Ans:
  • Full blocking --Sort, Aggregate etc...
  • Partial Blocking -- Merge, Union, Union All etc...
  • Q: Could you tell me sort transform properties?
  • Q: How many ways we convert the data?
  • Ans:
  • a) Data conversion transform
  • b) Expressions (DT_I4,Dt_Str etc...)
  • c) Source level/ target level directly [Advanced tab/ Show advanced editor]
  • Q: When do we go for Transform and when do we go for expression?
  • Ans: If there is an expression editor, take Type cast expression. Otherwise take transform.
  • Q: How many operations does aggregate transfor perform?
  • Ans: Seven--Sum,Avg,Min,Max,Count,Count Distinct, Group
  • Q: How do you identify that Derived column Transform is required?
  • Ans: If the business logics need to be implemented on every row.
  • Q: How do you merge rows in SSIS?
  • Ans: Union all,Marge and Marge Join Transforms
  • Q: Difference between Union all and Merge?
  • Ans:Refer to notes
  • Q: What is the basic protocol to merge rows?
  • Ans: Input structures should match.[No of cols and order of data types]
  • Q: What do you mean by sort key position 0, 1, 2?
  • Ans:
  • 0-->Unsorted
  • 1-->First column sorting
  • 2-->Second column sorting
  • Ex: PNM,PLOC,PID, need PLOC indescending and PNAME in ascending.
  • PID-->0
  • PLOC-->1
  • PNM-->2
  • Q: When do we go for Joins?
  • Ans: Joins help to get multiple columns from multiple tables.
  • Q: What is Join type?
  • Ans: The type of relationship you establish between multiple objects [usually between two objects]
  • Q: Do we have right join in SSIS?
  • Ans: Merge does not have right join but if we swap and perform left join, then it is right join.
  • Q: How many merge/ merge joins/ union all required for joining 4 inputs?
  • Ans:
  • Union All: Only one
  • Merge: N-1: 3
  • Merge Join: N-1: 3
  • Q: For every input record I would like to do one SQL operation (specially update), which transform required?
  • Ans : OLEDB command
  • Q: How do we present OLEDB complaint parameters?
  • Ans: ?
  • First ?: Parameter 0
  • Second ?: Parameter 1
  • Third ? : Parameter 2
  • etc...
  • Q: How do you process MVS or mainframe / cobol files data in SSIS?
  • Ans: Unpivot transform
  • Q: How look up is different from Merge join?
  • Ans:
  • Read for differences material
  • Lookup=Merge inner join + unmatched handling + Cache mechanism
  • Q: When do we go for Full cache, Partial Cache and No cache?
  • Ans: No cache-- Fresh data / Live data lookup
  • Full Cache-- If the lookup object not changing frequently
  • Partial Cache--If the lookup object changing sometimes
  • Q: What is the advantage of cache? and What it is?
  • Ans:
  • Cache prestores data so reduces the burden and traffic on the object.
  • It is a file, saved with .caw.
  • Q: Lookup two basic rules to operate?
  • Ans: a)Lookup object
  • b) Condition
  • c) Return columns
  • Q; What are the lookup objects supported in SSIS?
  • Ans: Table/ Cache File
  • Q: Incase of no match in the lookup what happens?
  • Ans: It allows to do either of the four operations
  • a) Fail component b)Ignore failure c) Redirect to No Match d)Redirect to Error Output
  • Q: Incase of multiple matches, which match it returns?
  • Ans: First Match
  • Q: What is the structure of unmatched data?
  • Ans: Similar to source
  • Q: What kind of difference you find when you go for No cache,Full cache and partial cache?
  • Ans:
  • No cache--> More burden, more traffic, more time
  • Full Cache-->Less burden, Less traffic, less time
  • Partial Cache--> Avg burden, Avg traffic, Avg time
  • Q: If the cache size increased or more, what is the impact of it and how do you reduce?
  • Ans:
  • More cache size leads to more time operation and more storage operation.
  • To take required cache, then write a customized query in the lookup transform or adjust the memory in the advanced tab.
  • use result of sql query: select did,dnm from dept where did in (10,20,30)
  • Q: What is the default join of Lookup?
  • Ans: Inner join
  • Q: How do we implement left join using lookup?
  • Ans: Using the below option:
  • ignore failure
  • Q: Difference between Fuzzy lookup and normal lookup?
  • Ans:
  • Fuzzy lookup: Similarity match on string values
  • Normal lookup: Exact match on numeric, string and date values
  • Normal lookup gives required return columns, where as fuzzy lookup additionally gives three columns
  • [Confidence, Column similarity, Row similarity]
  • Q: What is LineageID?
  • Ans: For every column system assigns a unique ID, i.e Lineage ID.
  • Q: How do we export multiple flatfiles / images into a table column for auditing?
  • Ans: Import Column Transform
  • Q: How many you have developed till this date?
  • Ans: I never constructued script for my applications, they are created by the script developers.
  • I know how to call and use methods and variables.
  • Q: What is the difference between script component and script task?
  • Ans: Script task available at control flow, script component available at Dataflow.
  • Script component has source, target and destination options.
  • Q: What is the meaning of Dts.TaskResult = (int)ScriptResults.Success?
  • Ans: It returns success state message to Package.It the system not reaching this statement means there is a problem in the script.
  • Q: What is a script hierarchy?
  • Ans: Namespace-->Class-->Method/Variables
  • Q:How do you control the tasks execution?
  • Ans: By using precedence constraints
  • Q: Explain constraints of SSIS?
  • Ans: a) Success b) Failure c) Completion (either success / failure)
  • Q: How many Precedence options available in SSIS?
  • Ans: Four options
  • a) Constraint b) Expression c) Expression and constraint d) Expression or constraint
  • Q: Whatis logical and in precedence constraint?
  • Ans:
  • Logical Ans -ensure all precedence conditions satisfaction.
  • Q: How do we pass parent package variable easily in SSIS?
  • Ans: By using execute package task
  • Q: What do you mean by Execute Outofprocess true?
  • Ans:The package runs seperately with its process or execution threads
  • Q: Can you give some examples of Execute Process task?
  • Ans: a) Unzip/ Zip folder b) Software or patches iinstallation c) Working with windows commands and powershell objects e) Reuable components (.dll,.com) f) batch file operations (.bat)
  • Q: How do you ensure file existence and continue with process?
  • Ans: By using WMI event watcher task
  • Q: How do you ensure how many applications running now?
  • Ans: WMI Reader Task
  • Q: How do we identify current memory statistics?
  • Ans: WMI Reader Task
  • Q: What is Task host container?
  • Ans: Every task itself is a container Ex: DataFlow: It has set of sub components and features
  • Q: How do you transfer sql server objects from one instance to another?
  • Ans: Transfer tasks
  • Q: How do we process SSAS objects in SSIS?
  • Ans: There are four tasks
  • a) Script task b) Analysis Services Processing Task c) Analysis Services Execute DDL task d) Execute Process Task
  • Q: What task helps to load dimensions and facts in SSIS?
  • Ans: Analysis Services Processing Task
  • Q; Which task helps to create/ modify objects in cube?
  • Ans: Analysis Services Execute DDL Task
  • Q: What is Hitcount in SSIS?
  • Ans: It is used in the BreakPoint level to control the number of hits.
  • Q:How do customers provide dummy files/ actual files for your Loading?
  • Ans: They keep in FTP Server
  • Q: How do you pass your pkgs, unit test docs etc... to customer?
  • Ans: There are different projects different ways, but in my current project we keep them in FTP server.
  • Q: How do you call web method in SSIS?
  • Ans: By using WebService Task
  • Q:What is logging and advantages of it, how do you implement in your project?
  • Ans:
  • Capturing log information (package components execution statistics) at specified events is called as logging.
  • In my OLD project(version 2008 R2) we used a custom database to log.
  • In latest project (post 2012) we are using existing catalog database logging.
  • Advantages:
  • a) Identify the execution process
  • b) Identify errors and rectify
  • c) Identify performance issues and eliminate
  • d) To identify regular intervals running and load statistics
  • Q: What is Logprovider?
  • Ans: The object which holds / captures log info is called as Log Provider.
  • Ex: Log Provider for File
  • Log Provider for table
  • Etc...
  • Q: How do you implement restartability in SSIS?
  • Ans: Checkpoint mechanism
  • Q: What is checkpoint mechanism?
  • Ans:
  • Implements restartability by keeping the stopped or failed point.
  • Q: What are the checkpoint options available?
  • Ans:
  • a) Checkpoint filename
  • b)Checkpoint usage
  • c)Save Checkpoints
  • Q: Can we take checkpoins and transactions in a single package?
  • Ans: NO, the reason is both are independent and contrary operations.
  • Checkpoint move forward where as Transaction rollbackword.
  • Q: What is the service does transaction use?
  • Ans: DTC--Distributed transaction coordinator
  • Q: What is Transaction?
  • Ans: Set of tasks executed like a single request, if all tasks executed successfully, then trnasaction is successful oterwise transaction fails and rollback the previously succesful tasks.
  • Q: How many transaction options available?
  • Ans:
  • Three options
  • a) Supported--Joins to the existing transaction
  • b) Required-- Joins if exists or starts new transaction
  • c) Not supported--Will not participate in the transaction process
  • Q: How do we implement transaction coding wise?
  • Ans:
  • Using Begin Transaction and End transaction statements in the programming and query languages.
  • Q: How do we run packages without SSDT?
  • Ans: Using commandline utilities a.DTEXECUI b.DTUtil c.DTEXEC d.
  • Q; How do we call packages in .Net Program?
  • Ans:
  • Using the command DTExec.exe with the below syntax.
  • DtExec.exe\Dts [Refer to 105page]
  • Q: What is configuration and how many configurations SSIS has?
  • Ans:
  • Configurations are important package settings [such as Sourcem, Target connection strings, Databases,Paths etc...] and user external values.
  • SSIS has 5 configurations
  • a) XML File
  • b) SqlSever
  • c)ParentPackage Variable
  • d) Environment Variable
  • e)Registry Variable
  • Q: What is deployment and how many models available?
  • Ans: Moving the packages and relevant files from one environment to other environment is called deployment.[Dev-->Test,Test-->Prod]
  • There are two ways
  • a) Legacy approach--Package Deployment [Since 2005...]
  • b) Modern Approach--Project Deployment [Since 2012]
  • Q: How do you implement Package deployment and how many modes available?
  • Ans:
  • Package by package deployed in this model.
  • a) Need to have Manifest file
  • b) Use manifest file to deploy into File System [to a Folder deployment] or Sqlserver [SSMS-->IS].
  • Q: What is manifest file?
  • Ans: The file which has deployment settings [Packages, config files and other files]
  • Q; How do we deploy requried packages?
  • Ans: Three ways
  • a) Open manifest file and remove the unnecessary packages
  • b) Remove packages from deplopyment folder
  • c) Exclude packages at solution expplorer and then Build->Solution
  • Q: Major differences between Package and Project deployment features?
  • Ans:
  • Package deployment + Versioning + Catalog Logging + Parameters and Environments
  • Q: How do you implement project deployment?
  • Ans:
  • a) Prepare Catalog Database
  • b) Deploy from SSDT
  • Q: What types of data loadng you have in the project?
  • Ans: In my DWH, there are hundreds of tables and multiple types of loading.
  • a) Direct Lod (Truncate and Load)
  • b) Incrmental Load (Direct b/ Delta Load)
  • c) Incrmental Load (SCD)
  • d) Incremental Load (CDC)
  • Q: How SCD is different from CDC?
  • Ans:
  • Source changes carry forwarding is called as CDC [Inserts,updates and deletions]
  • SCD handle new and old records by doing Insert and Update operations.
  • Q: Which SCD method you use most in real time?
  • Ans:
  • Depending on table situation we go for SCD type.
  • Some tables are Non history, Some tables are full history and some are partial history.
  • But I found mosty SCD date process for analytical reporting purpose.
  • Q: Explain about SSIS Isolation Levels?
  • Ans:
  • There are 7 options for this setting.
  • Unspecified: A different isolation level than the one specified is being used, but the level cannot be determined. Huh? I think that’s just another way of saying that the isolation level isn’t set for this container but that it is set for the package, or container that the task resides in.
  • Chaos: The same as ReadUncommitted but it checks the isolation level of pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
  • ReadUncommitted: Does not lock so a transaction can read uncommitted rows (dirty read).
  • ReadCommitted: Can only read records from committed transactions and issues a lock during the read. However it does not prevent records from being added, deleted or changed which can allow for a repeatable read (phantom read).
  • RepeatableRead: Locks are held until the transaction completes. It also means that the transaction cannot read data that has changed but has not been committed (ReadCommitted).
  • Serializable: Locks the entire data set until the transaction completes. It guarantees that the result set does not change during the transaction
  • Snapshot: The transaction takes a snapshot of the read at the beginning of the transaction. As such, data will not change. This property cannot be set for a package. It is an available option but it throws an error.