ssis interview questions in accenture

• 1) What is SSIS? …
• 2) What is SSIS control flow integration? …
• 3) What is Data Transformation? …
• 4) What kind of variables can you create? …
• 5) What kind of containers can you use with SSIS packages? …
• 6) How to create the deployment utility? …
• 8) What is the Manifest file in SSIS?

Looking for the SSIS (SQL Server Integration Services) job? Are you expertise in SSIS 2012 to SSIS 2016 versions? Do you need assistance in Informatica/ ETL Developer with SSIS? Do you have expertise in all platforms and technologies including SSIS, Informatica, Teradata, Oracle, SQL Server, Hadoop, SnapLogic etc? Having experience in OLTP and OLAP environments will be helpful in achieving career target. Top companies are looking for various positions including SSIS developer, MSBI consultant, ETL/Informatica developer, Power BI developer, Business intelligence consultant, senior software engineer, SSIS developer etc. SQL Server Integration Services is a tool which is part of the Microsoft SQL Server database software build to perform various data migration tasks. SSIS is a platform used for data integration and workflow applications. Have a look at SSIS (SQL Server Integration Services) job interview questions and answers to be used for interview success.

Accenture ssis interview questions

Interviews for Top Jobs at Accenture

Ssis Developer Interview

Ssis Developer Interview

What are the important components of SSIS package?

The important component in SSIS package are

Explain the term data transformation.

Ans: The procedure that permits you to remove explicit information out of its source is called data transformation.When that is done,it at that point oversees and moves it to the record based on your personal preference (usually, it is at the end of the file).

Get ahead in your career by learning ssis through hkrtrainings ssis training.

Ans: Some of the different assignments that SSIS handles are

Ans: SSIS can be referred to as an evolved version of DTS (Data Transformation Services) and has been re-composed without any preparation to beat the confinements of DTS which was accessible in SQL Server 2000 and prior renditions. A huge improvement is an isolation of the control/workstream from the information stream and the capacity to utilize a cushion/memory arranged engineering for information streams and changes which improves the overall execution.

Ans: Presently, in case youre simply beginning with SSIS, comparative inquiries may put on a show of being precarious – particularly when the past inquiry spun around information change. Similarly, as in most different cases, data flow in SSIS alludes to the procedure of data going starting with one point then onto the next. At the end of the day, information streams from a source to the objective area.

Ans: On the off chance that you are inexperienced with SSIS Expressions, it, undoubtedly implies that you are not a specialist and your SSIS experience is essential, so it is imperative to turn out to be comfortable with this element. SSIS Expressions can be utilized to channel data, to work with parameters, connect data, and make conditions. As such, it assists with working with dynamic associations, different conditions as well as working with loops.

Ans: SSIS control stream permits you to program in your own way as to how the assignments will run by utilizing the consistent connectors between undertakings. You have an option of three fundamental sensible connectors that you can utilize: complete, success and failure. Besides, you can utilize the FX (articulation) to deal with increasingly complex conditions that are beyond your control.

Ans:There are three components of data flow in SSIS

Ans: In the SSIS environment you get an option to choose among three types of containers, they are : sequence, for loops and each loop, respectively.

A for loop container is the thing that you can use to execute your undertakings to a specific number of times. For instance, you have to refresh records multiple times, you can put the errand that refreshes the records inside this for loop container and indicates 10 as the finish of the loops. by utilizing the for loop container, you dont need to make either ten unique bundles do a similar assignment or need to run the entire bundle multiple times when you plan your activity.

A sequence container is a basic method to bunch comparative errands. Think about a succession container as an association holder for increasingly complex SSIS bundles.

A for each loop container will be helpful when you dont know early how often an errand ought to perform. for example, suppose that you need to erase all the records inside an organizer, however, you dont have the foggiest idea what number of documents are there at a specific time, by utilizing for each circle, it can experience the assortment of records and erase them for you once the assortment is purged out, it realizes the time as to when to end.

Ans: The SSIS data flow task refers to the siphoning instrument that moves information from the origin to goal. In any case, on account of SSIS, you have significantly more command over what occurs all the way. Truth be told, you have a lot of out of the case change parts that you click on together to clean and control the information while it is in the information pipeline. Similarly, as the control stream handles the principle work process of the bundle, the Data Flow handles the change of information.Nearly anything that controls information falls into the Data Flow Category.

If you have any doubts on SSIS, then get them clarified by Industry experts team on our ssis tutorial !

Ans: The data profiling task is the way toward examining the source information to all the more likely comprehend what condition the information is in, regarding neatness, examples, numbers or nulls, etc. Information profiling tasks generally are utilized toward the start of the advancement cycle to help the structure of the goal database blueprint. Note that this errand isnt utilized when you build up the ordinary repeating ETL bundles.

Ans: In SSIS Multicast transformation, as the name suggests, is the process that can send solitary data input to numerous yield ways without any problem. You might need to utilize this chance to send away numerous goals cut in various manners. The multicast change is like the Split Transformation because both send information to different yields. In any case, you cannot determine the conditions for which some portion of the information will be in which yield in the Multicast transformation

Ans: In SSIS Merge transformation can blend information from two ways into a solitary yield. The Transform is helpful when you desire to break out your Data Flow into a way that manages certain blunders and afterward consolidate it again into the fundamental Data Flow downstream after the mistakes have been taken care of. Its likewise helpful if you wish to blend information from two Data Sources.

However, in SSIS the Union All Transformation works similarly as the Merge Transformation, yet it doesnt require the information to be arranged. It takes the yields from numerous sources or changes and joins them into a solitary outcome set.

Ans: In SSIS merge transformation is known to consolidate two paths into a solitary path. It is valuable when you need to break out information into a way that handles blunders after the mistakes are dealt with, the information is converged once more into downstream or you need to combine 2 information sources.

Ans: Merge transformation has a few limitations and they are as follows:

First is that it needs the Data to be properly sorted in an order

Secondly, the attributes of Data type, data length, and other Metadata should be similar to each other before they can be merged.

Ans: The OLE DB Command Transform is a segment intended to execute a SQL Statement for each line in an information stream. This errand is practically equivalent to an ADO Command Object being made, arranged, and executed for each line of an outcome set. The info stream gives that information to parameters that can be set into the SQL Statement that is either an Inline proclamation or a put away methodology call.

Ans: The package task is also known as the Execute Package task in SSIS refers to the one of the most essential elements of SSIS as this component is responsible for giving the developer the functionality to call the other SSIS packages right from the origin of their workflow. We can use this at any given time.

Ans: In SSIS a task undertaking will possibly execute if the condition that is set by the priority imperative going before the errand is met. By utilizing these limitations, we will pick diverse execution ways relying upon the achievement or disappointment of different tasks.

Success – Workflow will continue when the first compartment executes effectively. It is shown in control flow by a strong green line.

Failure – Workflow will continue when the former compartments execution brings about disappointment. It is demonstrated in control flow by a strong red line.

Completion – Workflow will continue when the first compartments execution finishes, paying little mind to progress or disappointment. It is shown in charge by a strong blue line.

Expression/Constraint with logical AND – work process will continue when determined articulation and imperatives assess to valid. It is demonstrated in charge stream by a strong shading line alongside a little fx symbol close to it.

Ans: A checkpoint is a property in SSIS which empowers the venture to restart from the purpose of disappointment. At the point when we set the property to a genuine bundle, make the checkpoint document that stores the data about bundle execution and use it to restart the bundle from the purpose of disappointment. On the off chance that the bundle runs effectively, the checkpoint document is erased, and afterward re-made whenever the package runs.

Ans: In SSIS a task is something that you would issue to the database, to get certain ideal outcomes. Altogether, there are two kinds of tasks in SSIS – control stream assignments and database support ones.

Ans: I am running an Execute SQL Task articulation in my SSIS package. The Execute SQL Task is running SQL and watching that the tables have over 1000 lines.If they have fewer than 1000 lines, the package will fail.

If you have any doubts on ssis, then get them clarified from ssis Industry experts on our ssis community !

Ans: The procedure of deployment is where you would move the records from the “sending stage” to the “execution stage”. You could state that it resembles taking records from their areas and delivering them off for execution.

Ans: Its critical to know however much as could be expected about the information in the SSIS inquiries questions. As the name likely suggests, a data flow engine is liable for the progression of information through should SSIS. The engine uses what are known as supports to make a consistent and overly quick progression of information from its a source to the goal.

Ans: SSIS Container is a legitimate gathering of tasks that permits dealing with the extent of a task together. The ideal answer is naming every one of them :

Ans: In SSIS solution explorer is the screen where you can view and access all the information sources, information sources perspectives, ventures, and different random documents.

Ans: While gathering information from various sources and composing it to a goal, association chiefs are useful. Association supervisor encourages the association with the framework that incorporates data that resembles information supplier data, server name, verification system, database name, and so forth.

Ans: An SSIS breakpoint empowers you to stop the proper implementation of the bundle in the business insight advancement studio during the investigating or the vital advancement of an SSIS package.

Ans: In SSIS, the process of event logging permits the developer to choose a particular occasion of an assignment or a bundle that requires logging. It is gainful at times if you are investigating your bundle to comprehend the presentation package.

Ans: The tasks in SSIS operate by utilizing buffers; a data flow buffer is like an in-memory sort of a virtual table that is utilized to hold data where it is supposed to be.

Ans: In the computing world, extract, transform, the load is the general methodology of duplicating information from at least one source into a goal framework that speaks to the information uniquely in contrast to the source or an unexpected setting in comparison to the source.

Ans: Usually,it is not recommended to not save the data, however, in the normal course, the Checkpoint data does not get saved for the For Loop containers and the For Each Loop container.

Ans: The phase of conditional split transformation is just as similar to the IF condition,that is used to check for all the listed out conditions that are based on the conditional evaluation.

Ans: The various kinds of data viewers that are available in SSIS include

Ans: Right on the event handlers tab, work processes can be designed to react to bundle occasions. For example, you can arrange the work process when any task stops, comes up short or starts.

Ans: it is possible to do so in two ways-

1. Inside the package, you could include a Send Mail Task in the occasion handlers,

2. You can even set the notice in the SQL Agent when the package runs.

Ans: Various connection types that work within SSIS are :

Ans: You can design a SQL Server Agent Job with work step kind of SQL Server Integration Services Package, the activity summons the dtexec order line utility inside to execute the bundle. You can run the activity (and thus the SSIS bundle) on request or you can make a calendar for a one time need or on a reoccurring premise. Allude to this tip to become familiar with it.

Ans: To be honest, Late Arriving Dimension is unavoidable, to deal with these we can make fake measurements with normal/business keys and keep the remainder of the properties as invalid or default. So when real measurement shows up, the fake measurement is refreshed with Type 1 change. This likewise alludes as Inferred Dimensions.

We have the perfect professional SSIS Tutorial for you. Enroll now!

Ans: Yes, you can. You can have a Visual Studio venture in C# like a web application a Windows work area application, a support application and conjure SSIS items to program errands without SSIS ventures. As such, you could make your control streams, information streams, associations utilizing just C# code or Visual Studio code. In the vast majority of the cases, it isnt down to earth to do it, since it might take a longer time. In any case, it very well may be vital if the undertaking in the SSIS venture has some usefulness restriction that could be shrouded by the code in C#.

Ans: Execution trees show how the package utilizes buffers and strings. At run time, the information stream motor separates Data Flow task activities into execution trees. These execution trees indicate how supports and strings are assigned in the bundle. Each tree makes another cradle and may execute on an alternate string. At the point when another support is made, for example, when an incompletely blocking or blocking change is added to the pipeline, extra memory is required to deal with the information change and each new tree may likewise give you an extra worker string.

Ans: The quickest approach to do gradual burden is by utilizing the Timestamp section in the source table and afterward putting away last ETL timestamp, In the ETL process pick all the lines having Timestamp more prominent than the put-away Timestamp to pick just new and refreshed records.

Ans: Here is a list of some best practices of SSIS-

Explain solution Explorer in SSIS

Solution Explorer in SSIS Designer is a screen where you can view and access all the data sources, data sources views, projects, and other miscellaneous files.

FAQ

What is the difference between checkpoint and breakpoint in SSIS?

• Control Flow (Stores containers and Tasks)
• Data Flow (Source, Destination, Transformations)
• Event Handler (sending of messages, Emails)
• Package Explorer (Offers a single view for all in package)
• Parameters (User Interaction)

What is the difference between SQL and SSIS?

Can I run a SSIS package by using a stored procedure?