Interview Questions.

Question 1. Explain What Is Ssis?

Answer :

SSIS or SQL Server Integration Services (SSIS) is part of Microsoft SQL Server, which may be used to accomplish a wide variety of data migration duties.

Question 2. Mention What Are The Important Components Of Ssis Package?

Answer :

The vital factor in SSIS package deal are:

Data go with the flow
Control waft
Package explorer
Event handler
T-SQL Interview Questions
Question three. Explain What Is Solution Explorer In Ssis?

Answer :

Solution Explorer in SSIS Designer is a display screen where you could view and access all the information sources, facts assets views, projects, and other miscellaneous files.

Question four. Explain What Does It Mean By Data Flow In Ssis?

Answer :

Data go with the flow in SSIS is nothing but the waft of data from the corresponding sources to the goal locations.

T-SQL Tutorial
Question five. Define What Is “mission” In Ssis?

Answer :

Task in SSIS is a completely a great deal just like the approach of any programming language that represents or includes out an man or woman unit of work.  Tasks are categorized into  categories

Control Flow Tasks
Database Maintenance Tasks
SAS Programming Interview Questions
Question 6. Explain What Is Ssis Package?

Answer :

A bundle in SSIS is an prepared series of connections like data go with the flow elements, control activities, occasion handlers, parameters, variables, and configurations. You gather them by using both constructing it programmatically or by means of graphical layout equipment that SSIS offers.

Question 7. Explain What Is A Container? How Many Types Of Containers Are There In Ssis?

Answer :

In SSIS, a field is a logical grouping of obligations, and it allows to manipulate the scope of a venture collectively.

Types of containers in SSIS are

Sequence container
For loop container
Foreach loop field
Task host container
SAS Programming Tutorial SSRS(SQL Server Reporting Services) Interview Questions
Question eight. Explain What Is Precedence Constraint In Ssis?

Answer :

Precedence Constraint in SSIS enables you to define the logical sequence of duties inside the order they ought to be executed.  You can connect all of the tasks the usage of connectors- Precedence Constraints.

Question nine. Explain What Variables In Ssis And What Are The Types Of Variables In Ssis?

Answer :

Variable in SSIS is essentially used to keep values.  In SSIS, there are two forms of variables device variable and consumer variable.

MYSQL DBA Interview Questions
Question 10. Explain What Is A Checkpoint In Ssis?

Answer :

Checkpoint in SSIS permits the venture to restart from the factor of failure. Checkpoint document stores the statistics approximately the package execution, if the package deal run correctly the checkpoint record is deleted otherwise it'll restart from the factor of failure.

Question eleven. Explain What Is Connection Managers In Ssis?

Answer :

While amassing facts from special assets and writing it to a destination, connection managers are beneficial.  Connection manager enables the connection to the gadget that encompass data’s like facts issuer records, server call, authentication mechanism, database call, and so on.

SQL Server Management Studio Interview Questions
Question 12. Explain What Is Ssis Breakpoint?

Answer :

A breakpoint permits you to pause the execution of the bundle in business intelligence improvement studio at some point of troubleshooting or development of an SSIS bundle.

T-SQL Interview Questions
Question thirteen. Explain What Is Event Logging In Ssis?

Answer :

In SSIS, occasion logging permits you to pick any precise occasion of a challenge or a package to be logged. It could be very helpful while you are troubleshooting your bundle to apprehend the performance bundle.

Question 14. Explain What Is Logging Mode Property?

Answer :

SSIS programs and all of the associated tasks have a assets referred to as LoggingMode.   This property accepts three feasible values

Disabled: To enable logging of the component

Enabled: To disable logging of the component

UseParentSetting: To use discern’s putting of the thing

Question 15. Explain What Is A Data Flow Buffer?

Answer :

SSIS operates the usage of buffers; it's far a form of an in-reminiscence digital desk to preserve facts.

Oracle MySQL 5.6 Database Administrator Interview Questions
Question sixteen. For What Data Checkpoint Data Is Not Saved?

Answer :

Checkpoint facts is not saved for ForEach Loop and ForLoop packing containers.

Question 17. Explain What Is Conditional Split Transactions In Ssis?

Answer :

Conditional break up transformation in SSIS is much like IF circumstance, which assessments for the given condition based at the circumstance assessment.

DB2 SQL Programming Interview Questions
Question 18. List Out The Different Types Of Data Viewers In Ssis?

Answer :

Different styles of data visitors in SSIS encompass:

Grid
Histogram
Scatter Plot
Column Chart
SAS Programming Interview Questions
Question 19. Mention What Are The Possible Locations To Save Ssis Package?

Answer :

You can keep SSIS package at

SQL Server
Package Store
File System
Question 20. What Will Be Your First Approach If The Package That Runs Fine In Business Intelligence Development Studio (bids) But Fails When Running From An Sql Agent Job?

Answer :

The account that runs SQL Agent Jobs might not have the desired permission for one of the connections to your bundle. In such instances, both you can create a proxy account or increase the account permissions.

SQLite Interview Questions
Question 21. Explain What Is The Role Of Event Handlers Tab In Ssis?

Answer :

On the event handlers tab, workflows can be configured to reply to package activities.  For example, you could configure workflow when any task stops, fails or starts offevolved.

Question 22. Explain How You Can Notify The Staff Members About Package Failure?

Answer :

Either inside the package deal you can upload a Send Mail Task inside the occasion handlers, or you can even set notification within the SQL Agent while the package runs.

Question 23. How Can An Ssis Package Be Scheduled To Execute At A Defined Time Or At A Defined Interval Per Day?

Answer :

You can configure a SQL Server Agent Job with a process step sort of SQL Server Integration Services Package, the process invokes the dtexec command line application internally to execute the bundle. You can run the process (and in turn the SSIS bundle) on demand or you may create a agenda for a one time want or on a reoccurring foundation. Refer to this tip to examine extra about it.

SQL Server Analysis Services (SSAS) Interview Questions
Question 24. Mention How Would You Deploy An Ssis Package On Production?

Answer :

To installation SSIS package we need to execute the happen documents and need to decide whether or not to install this into File System or onto SQL Server.  Alternatively you may additionally import package from SSMS from SQL Server or File System.

SSRS(SQL Server Reporting Services) Interview Questions
Question 25. Explain How To Handle Early Arriving Facts Or Late Arriving Dimension?

Answer :

Late Arriving Dimension are unavoidable, to deal with those we will create a dummy dimensions with herbal/enterprise key and maintain the relaxation of the attributes as null or default. So while actual measurement arrives, the dummy dimension is updated with Type 1 exchange. This is likewise referred as Inferred Dimensions.

Question 26. Explain How Can You Do An Incremental Load?

Answer :

The fine and fastest manner to do incremental load is by way of using Timestamp column in the supply desk and storing the final ETL timestamp.

Question 27. How Would You Do Logging In Ssis?

Answer :

Logging Configuration affords an in-built function that could log the detail of various events like onError, onWarning and so forth to the diverse options say a flat report, SqlServer table, XML or SQL Profiler.

MYSQL DBA Interview Questions
Question 28. How Would You Do Error Handling?

Answer :

A SSIS bundle ought to especially have two forms of errors

a) Procedure Error: Can be handled in Control flow through the precedence manipulate and redirecting the execution float.

B) Data Error: is dealt with in DATA FLOW TASK buy redirecting the records go with the flow using Error Output of a element.

Question 29. How To Pass Property Value At Run Time? How Do You Implement Package Configuration?

Answer :

A assets cost like connection string for a Connection Manager may be handed to the pkg the usage of bundle configurations.Package Configuration offers distinctive alternatives like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.

Question 30. What Is Execution Tree?

Answer :

Execution timber demonstrate how bundle uses buffers and threads. At run time, the data drift engine breaks down Data Flow assignment operations into execution timber. These execution trees specify how buffers and threads are allocated in the bundle. Each tree creates a new buffer and might execute on a extraordinary thread. When a new buffer is created such as when a partially blocking off or blocking transformation is introduced to the pipeline, extra reminiscence is required to handle the data transformation and every new tree might also come up with an additional worker thread.

Question 31. Difference Between Union All And Merge Join?

Answer :

a) Merge transformation can take delivery of most effective two inputs while Union all can take greater than  inputs

b) Data needs to be looked after before Merge Transformation while Union all does not have any situation like that.

Question 32. How Would You Restart Package From Previous Failure Point?What Are Checkpoints And How Can We Implement In Ssis?

Answer :

When a package is configured to use checkpoints, facts approximately package execution is written to a checkpoint record. When the failed package deal is rerun, the checkpoint record is used to restart the package deal from the factor of failure. If the package runs efficaciously, the checkpoint record is deleted, and then re-created the next time that the bundle is run.

Question 33. Where Are Ssis Package Stored In The Sql Server?

Answer :

MSDB.Sysdtspackages90 shops the real content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the assisting roles.

SQL Server Management Studio Interview Questions
Question 34. Difference Between Asynchronous And Synchronous Transformations?

Answer :

Asynchronous transformation have exclusive Input and Output buffers and it's miles as much as the element dressmaker in an Async aspect to offer a column structure to the output buffer and hook up the facts from the input.

Question 35. How To Achieve Parallelism In Ssis?

Answer :

Parallelism is carried out using MaxConcurrentExecutable property of the bundle. Its default is -1 and is calculated as range of processors + 2.

Question 36. How Do You Do Incremental Load?

Answer :

Fastest manner to do incremental load is by means of the usage of Timestamp column in source desk and then storing ultimate ETL timestamp, In ETL method choose all of the rows having Timestamp greater than the saved Timestamp that allows you to select simplest new and updated records.

Oracle MySQL five.6 Database Administrator Interview Questions
Question 37. How To Handle Late Arriving Dimension Or Early Arriving Facts.?

Answer :

Late arriving dimensions someday get unavoidable 'coz put off or mistakes in Dimension ETL or may be due to good judgment of ETL. To manage Late Arriving information, we will create dummy Dimension with herbal/business key and preserve rest of the attributes as null or default.  And as quickly as Actual measurement arrives, the dummy size is updated with Type 1 change. These are also referred to as Inferred Dimensions.

Question 38. If You Want To Send Some Data From Access Database To Sql Server Database. What Are Different Component Of Ssis Will You Use?

Answer :

In the records drift, we are able to use one OLE DB source, records conversion transformation and one OLE DB destination or SQL server vacation spot. OLE DB supply is records supply is useful for studying information from Oracle, SQL Server and Access databases. Data Conversion transformation would be had to cast off datatype abnormality due to the fact that there's difference in datatype among the two databases (Access and SQL Server) mentioned. If our database server is stored on and package is administered from equal system, we can use SQL Server destination in any other case we want to use OLE DB vacation spot. The SQL Server destination is the vacation spot that optimizes the SQL Server.

Question 39. What Is Sql Server Integration Services (ssis)?

Answer :

SQL Server Integration Services (SSIS) is thing of SQL Server 2005 and later variations. SSIS is an agency scale ETL (Extraction, Transformation and Load) device which lets in you to expand information integration and workflow answers. Apart from facts integration, SSIS can be used to define workflows to automate updating multi-dimensional cubes and automating maintenance tasks for SQL Server databases.

Question forty. How Does Ssis Differ From Dts?

Answer :

SSIS is a successor to DTS (Data Transformation Services) and has been completely re-written from scratch to overcome the restrictions of DTS which turned into available in SQL Server 2000 and in advance variations. A large improvement is the segregation of the manipulate/paintings drift from the information waft and the capability to apply a buffer/reminiscence orientated structure for facts flows and ameliorations which enhance performance.

DB2 SQL Programming Interview Questions
Question 41. What Is The Control Flow?

Answer :

When you start working with SSIS, you first create a package which is nothing however a group of duties or package components. The manipulate glide lets in you to order the workflow, so that you can make sure tasks/components get achieved in the correct order.

Question forty two. What Is The Data Flow Engine?

Answer :

 The Data Flow Engine, additionally known as the SSIS pipeline engine, is chargeable for managing the go with the flow of facts from the supply to the vacation spot and acting changes (lookups, records cleansing and many others.).  Data flow makes use of reminiscence oriented structure, called buffers, at some stage in the information float and alterations which lets in it to execute extraordinarily fast. This way the SSIS pipeline engine pulls records from the source, stores it in buffers (in-reminiscence), does the requested adjustments in the buffers and writes to the vacation spot. The benefit is that it offers the quickest transformation because it takes place in memory and we do not need to level the facts for variations in maximum cases.

SQLite Interview Questions
Question 43. What Is A Transformation?

Answer :

A transformation virtually way bringing within the facts in a desired layout. For example you are pulling information from the supply and want to make sure simplest wonderful statistics are written to the destination, so duplicates are  removed.  Another instance is if you have grasp/reference statistics and want to tug best related records from the source and for this reason you want some type of lookup. There are around 30 transformation obligations to be had and this can be extended in addition with custom constructed obligations if needed.

Question 44. What Is A Task?

Answer :

A project may be very similar to a technique of any programming language which represents or incorporates out an man or woman unit of labor. There are widely two classes of tasks in SSIS, Control Flow obligations and Database Maintenance duties. All Control Flow responsibilities are operational in nature besides Data Flow responsibilities. Although there are around 30 manipulate float responsibilities which you could use to your package deal you may also broaden your own custom tasks with your choice of .NET programming language.

Question 45. What Is A Precedence Constraint And What Types Of Precedence Constraint Are There?

Answer :

SSIS lets in you to region as many as obligations you want to be placed on top of things waft. You can connect these kind of duties using connectors referred to as Precedence Constraints. Precedence Constraints assist you to outline the logical series of tasks within the order they need to be finished. You also can specify a situation to be evaluated earlier than the following undertaking inside the go with the flow is accomplished.

These are the types of priority constraints and the circumstance could be either a constraint, an expression or both Success (subsequent project can be completed best while the closing task completed efficaciously) or Failure (subsequent project might be achieved most effective when the remaining task failed) or Complete (subsequent task may be done no matter the ultimate undertaking was finished or failed).

Question forty six. What Is A Container And How Many Types Of Containers Are There?

Answer :

A box is a logical grouping of duties which lets in you to manipulate the scope of the tasks together.

These are the forms of bins in SSIS:

Sequence Container - Used for grouping logically associated duties together

For Loop Container - Used while you need to have repeating waft in package

For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a listing of files.

Apart from the above mentioned packing containers, there may be one extra container referred to as the Task Host Container which isn't always seen from the IDE, however each task is contained in it (the default field for all of the tasks).

Question forty seven. What Are Variables And What Is Variable Scope?

Answer :

A variable is used to save values. There are essentially two kinds of variables, System Variable (like ErrorCode, ErrorDescription, PackageName and many others) whose values you could use however can't exchange and User Variable that you create, assign values and study as needed. A variable can keep a fee of the records kind you have got chosen whilst you described the variable.

Variables will have a exclusive scope relying on in which it turned into described. For instance you can have package stage variables which can be handy to all the obligations inside the package deal and there may also be box degree variables which are reachable only to those duties which can be within the container.

Question 48. What Is An Ssis Proxy Account And Why Would You Create It?

Answer :

When we strive to execute an SSIS bundle from a SQL Server Agent Job it fails with the message "Non-SysAdmins had been denied permission to run DTS Execution job steps with out a proxy account". This error message is generated if the account below which SQL Server Agent Service is jogging and the job proprietor isn't always a sysadmin on the instance or the task step is not set to run under a proxy account associated with the SSIS subsystem. Refer to this tip to analyze more approximately it.

Question forty nine. How Can You Configure Your Ssis Package To Run In 32-bit Mode On sixty four-bit Machine When Using Some Data Providers Which Are Not Available On The sixty four-bit Platform?

Answer :

In order to run an SSIS package deal in 32-bit mode the SSIS undertaking belongings Run64BitRuntime needs to be set to "False".  The default configuration for this property is "True".  This configuration is an instruction to load the 32-bit runtime surroundings rather than 64-bit, and your packages will nonetheless run with none additional adjustments. The belongings can be discovered beneath SSIS Project Property Pages -> Configuration Properties -> Debugging.

Question 50. How Is Ssis Runtime Engine Different From The Ssis Dataflow Pipeline Engine?

Answer :

The SSIS Runtime Engine manages the workflow of the applications at some point of runtime, which means its function is to execute the duties in a described series.  As , you could define the sequence the use of precedence constraints. This engine is likewise chargeable for offering aid for occasion logging, breakpoints inside the BIDS dressmaker, package configuration, transactions and connections. The SSIS Runtime engine has been designed to aid concurrent/parallel execution of responsibilities in the package.

The Dataflow Pipeline Engine is responsible for executing the records glide responsibilities of the bundle. It creates a dataflow pipeline by way of allocating in-memory shape for storing records in-transit. This method, the engine pulls records from supply, shops it in reminiscence, executes the desired transformation within the facts saved in memory and in the end hundreds the facts to the destination. Like the SSIS runtime engine, the Dataflow pipeline has been designed to do its paintings in parallel by using growing multiple threads and allowing them to run more than one execution bushes/devices in parallel.