SSIS Interview Questions and Answers

21
381889

Friends,

In this post we are gonna discuss about the different interview questions you may face in interviews. I continue to add questions and answers to this post and hence please visit this page regularly to know more and more questions and answers.

• What do we mean by dataflow in SSIS?
Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse.

• What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an
opportunity to review the status of the data, variables and the overall status of the SSIS package.
10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the
object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option.

• Can you name 5 or more of the native SSIS connection managers?

1) OLEDB connection – Used to connect to any data source requiring an OLEDB connection (i.e.,
SQL Server 2000)
2) Flat file connection – Used to make a connection to a single file in the File System. Required for reading information from a File System flat file
3) ADO.Net connection – Uses the .Net Provider to make a connection to SQL Server 2005 or other
connection exposed through managed code (like C#) in a custom task
4) Analysis Services connection – Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
5) File connection – Used to reference a file or folder. The options are to either use or create a file or folder
6) Excel

• What is the use of Bulk Insert Task in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.

• What is Conditional Split transformation in SSIS?
This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions. For example, we can organize the transform for the students in a class who have marks greater than 40 to one path and the students who score less than 40 to another path.

• How do you eliminate quotes from being uploaded from a flat file to SQL Server? 
This can be done using TEXT QUALIFIER property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.

• Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName – Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name.
CheckpointUsage – Determines if/how checkpoints are used. Choose from these options: Never(default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
SaveCheckpoints – Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.

• What are the different values you can set for CheckpointUsage property ?
There are three values, which describe how a checkpoint file is used during package execution:
1) Never: The package will not use a checkpoint file and therefore will never restart.
2) If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written.
3) Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.

• What is the ONLY Property you need to set on TASKS in order to configure CHECKPOINTS to RESTART package from failure?
The one property you have to set on the task is FailPackageOnFailure. This must be set for each task or container that you want to be the point for a checkpoint and restart. If you do not set this property to true and the task fails, no file will be written, and the next time you invoke the package, it will start from the beginning again.

• Where can we set the CHECKPOINTS, in DataFlow or ControlFlow ?
Checkpoints only happen at the Control Flow; it is not possible to checkpoint transformations or restart inside a Data Flow. The Data Flow Task can be a checkpoint, but it is treated as any other task.

• Can you explain different options for dynamic configurations in SSIS?
1) XML file
2) custom variables
3) Database per environment with the variables
4) Use a centralized database with all variables

• What is the use of Percentage Sampling transformation in SSIS?
Percentage Sampling transformation is generally used for data mining. This transformation builds a random sample of set of output rows by choosing specified percentage of input rows. For example if the input has 1000 rows and if I specify 10 as percentage sample then the transformation returns 10% of the RANDOM records from the input data.

• What is the use of Term Extraction transformation in SSIS?
Term Extraction transformation is used to extract nouns or noun phrases or both noun and noun phrases only from English text. It extracts terms from text in a transformation input column and then writes the terms to a transformation output column. It can be also used to find out the content of a dataset.

• What is Data Viewer and what are the different types of Data Viewers in SSIS?
A Data Viewer allows viewing data at a point of time at runtime. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the runtime and how it looks like after the transformation occurred. The different types of data viewers are:

1. Grid
2. Histogram
3. Scatter Plot
4. Column Chart.

• What is Ignore Failure option in SSIS?
In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data(wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file.

• Which are the different types of Control Flow components in SSIS?
The different types of Control Flow components are: Data Flow Tasks, SQL Server Tasks, Data Preparation Tasks, Work flow Tasks, Scripting Tasks, Analysis Services Tasks, Maintenance Tasks, Containers.

• What are containers? What are the different types of containers in SSIS?
Containers are objects that provide structures to packages and extra functionality to tasks. There are four types of containers in SSIS, they are: Foreach Loop Container, For Loop Container, Sequence Container and Task Host Container.

• What are the different types of Data flow components in SSIS?
There are 3 data flow components in SSIS.
1. Sources
2. Transformations
3. Destinations

• What are the different types of data sources available in SSIS?
There are 7 types of data sources provided by SSIS: a.) Data Reader source b.) Excel source c.) Flat file source d.) OLEDB source e.) Raw file source f.) XML source g.) Script component

• What is SSIS Designer?
It is a graphical tool for creating packages. It has 4 tabs: Control Flow, Data Flow, Event Handlers and Package Explorer.

• What is Control Flow tab?
It is the tab in SSIS designer where various Tasks can be arranged and configured. This is the tab where we provide and control the program flow of the project.

• What is Data Flow tab?
This is the tab where we do all the work related to ETL job. It is the tab in SSIS Designer where we can extract data from sources, transform the data and then load them into destinations.

• What is the function of control flow tab in SSIS?
On the control flow tab, the tasks including dataflow task, containers and precedence constraints that connect containers and tasks can be arranged and configured.

• What is the function of Event handlers tab in SSIS?
On the Event handlers tab, workflows can be configured to respond to package events.
For example, we can configure Work Flow when ANY task Failes or Stops or Starts ..

• What is the function of Package explorer tab in SSIS?
This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.

• What is Solution Explorer?
It is a place in SSIS Designer where all the projects, Data Sources, Data Source Views and other miscellaneous files can be viewed and accessed for modification.

• How do we convert data type in SSIS?
The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.

• How are variables useful in ssis package?
Variables can provide communication among objects in the package. Variables can provide communication between parent and child packages. Variables can also be used in expressions and scripts. This helps in providing dynamic values to tasks.

• Explain Aggregate Transformation in SSIS?
It aggregates data, similar you do in applying TSQL functions like Group By, Min, Max, Avg, and Count. For example you get total quantity and Total line item for each product in Aggregate Transformation Editor. First you determine input columns, then output column name in Output Alias table in datagrid, and also operations for each Output Alias in Operation columns of the same datagrid. Some of operation functions listed below :
• Group By
• Average
• Count
• Count Distinct : count distinct and non null column value
• Min, Max, Sum
In Advanced tab, you can do some optimization here, such as setting up Key Scale option (low, medium, high), Count Distinct scale option (low, medium, high), Auto Extend factor and Warn On Division By Zero. If you check Warn On Division By Zero, the component will give warning instead of error. Key Scale option will optimize transformation cache to certain number of key threshold. If you set it low, optimization will target to 500,000 keys written to cache, medium can handle up to 5 million keys, and high can handle up to 25 million keys, or you can specify particular number of keys here. Default value is unspecified. Similar to number of keys for Count Distinct scale option. It is used to optimize number of distinct value written to memory, default value is unspecified. Auto Extend Factor is used when you want some portion of memory is used for this component. Default value is 25% of memory.

• Explain Audit Transformation ?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are :
1. Execution of Instance GUID : ID of execution instance of the package
2. PackageID : ID of the package
3. PackageName
4. VersionID : GUID version of the package
5. Execution StartTime
6. MachineName
7. UserName
8. TaskName
9. TaskID : uniqueidentifier type of the data flow task that contains audit transformation.

• Explain Character Map Transformation?
It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Operations available here are:
1. Uppercase
2. Lowercase
3. Byte reversal : such as from 0x1234 to 0x4321
4. Full width
5. Half width
6. Hiragana/katakana/traditional Chinese/simplified Chinese
7. Linguistic casing

• Explain Conditional split Transformation ?
It functions as if…then…else construct. It enables send input data to a satisfied conditional branch. For example you want to split product quantity between less than 500 and greater or equal to 500. You can give the conditional a name that easily identifies its purpose. Else section will be covered in Default Output Column name.
After you configure the component, it connect to subsequent transformation/destination, when connected, it pops up dialog box to let you choose which conditional options will apply to the destination transformation/destination.

• Explain Copy column Transformation?
This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.

• Explain Data conversion Transformation?
This component does conversion data type, similar to TSQL function CAST or CONVERT. If you wish to convery the data from one type to another then this is the best bet. But please make sure that you have COMPATABLE data in the column.

• Explain Data Mining query Transformation?
This component does prediction on the data or fills gap on it. Some good scenarios uses this component is:
1. Take some input columns as number of children, domestic income, and marital income to predict whether someone owns a house or not.
2. Take prediction what a customer would buy based analysis buying pattern on their shopping cart.
3. Filling blank data or default values when customer doesn’t fill some items in the questionnaire.

• Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.

• Explain Merge Transformation?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions :
1. Data should be in sorted order
2. Data type , data length and other meta data attribute must be similar before merged.

• Explain Merge Join Transformation?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.

• Explain Multicast Transformation?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.

• Explain Percentage and row sampling Transformations?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.

• Explain Sort Transformation?
This component will sort data, similar in TSQL command ORDER BY. Some transformations need sorted data.

• Explain Union all Transformation?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.

• What r the possible locations to save SSIS package?
You can save a package wherever you want.
SQL Server
Package Store
File System

• What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.

• What is a workflow in SSIS?
A workflow is a set of instructions on how to execute tasks.
(It is a set of instructions on how to execute tasks such as sessions, emails and shell commands. a workflow is created form work flow mgr.

• What is the diff between control flow Items and data flow Items?
The control flow is the highest level control process. It allows you to manage the run-time process activities of data flow and other processes within a package.
When we want to extract, transform and load data within a package. You add an SSIS dataflow task to the package control flow.

• What are the main component of SSIS(project-architecture)?
SSIS archItecture has 4 main components
1.ssis service
2.ssis runtime engine & runtime executables
3.ssis dataflow engine & dataflow components
4.ssis clients

• Different components in SSIS package?
Control flow
Data flow
Event handler
Package explorer

• What are Connection Managers?
It is a bridge b/w package object and physical data. It provides logical representation of a connection at design time the properties of the connection mgr describes the physical connection that integration services creates when the package is run.

• What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.

• How to provide securIty to packages?
We can provide security in two ways
1. Package encryption
2. Password protection.

• What are Precedence constraints?
Constraints that link executable, container, and tasks wIthin the package control flow and specify condItion that determine the sequence and condItions for determine whether executable run.

• What is Design time Deployment in SSIS ?

When you run a package from with in BIDS,it is built and temporarily deployed to the folder. By default the package will be deployed to the BIN folder in the Package’s Project folder and you can configure for custom folder for deployment. When the Package’s execution is completed and stopped in BIDS,the deployed package will be deleted and this is called as Design Time Deployment.

21 COMMENTS

  1. GotBee.ru: ваш путь к успешному предпринимательству

    [img]https://data0.gallery.ru/albums/gallery/3370235-4d912-132637227-m750x740-ub2d22.jpg[/img]

    В мире бизнеса постоянно происходят изменения, и одним из ключевых аспектов успеха является умение адаптироваться к новым условиям. Биржа готового бизнеса [url=https://gotbee.ru/]https://gotbee.ru[/url] предлагает продажу готового бизнеса – это уникальное решение для тех, кто хочет начать свое дело или расширить существующий бизнес, а также для тех, кто ищет выгодные инвестиции.

    Готовый бизнес под любые запросы

    [img]https://data0.gallery.ru/albums/gallery/3370235-792b2-132637229-m750x740-u6663a.jpg[/img]

    Биржа готового бизнеса gotbee.ru – это уникальная площадка, которая предоставляет возможность выгодно купить или продать готовый бизнес. В каталоге представлен широкий выбор готовых бизнесов различных ниш и масштабов, что позволит выбрать наиболее подходящий вариант именно для вас. Кому будет полезна биржа GotBee.ru:

    • начинающим предпринимателям, ищущим перспективные направления для старта;
    • опытным бизнесменам, желающим расширить свой портфель активов или диверсифицировать риски;
    • инвесторам, заинтересованным в стабильных и прибыльных вложениях;
    • владельцам бизнеса, стремящимся продать свою компанию на выгодных условиях.

    Платформа обеспечивает безопасность сделок, прозрачность условий и возможность быстрого заключения договоров. Одним из основных преимуществ gotbee.ru является возможность быстро решить все вопросы, связанные с куплей или продажей бизнеса. Благодаря удобному интерфейсу и простой процедуре размещения объявлений, вы сможете быстро найти покупателя или продавца для вашего бизнеса. Процесс работы на бирже включает несколько этапов:

    • регистрация на платформе;
    • поиск подходящего предложения;
    • общение с продавцом для уточнения всех деталей;
    • заключение договора купли-продажи;
    • оплата и передача бизнеса новому владельцу.

    Почему стоит воспользоваться gotbee.ru

    [img]https://data0.gallery.ru/albums/gallery/3370235-df8a4-132637230-m750x740-ufa1f2.jpg[/img]

    Как уже было отмечено, биржа gotbee.ru будет полезна как опытным бизнесменам, которые хотят расширить свой бизнес за счет покупки готового проекта, так и начинающим предпринимателям, которые делают первые шаги в бизнесе и ищут готовое решение с минимальными рисками. Почему стоит воспользоваться биржей GotBee.ru? Ответ на этот вопрос достаточно прост:

    • Широкий выбор предложений – на платформе представлены разнообразные бизнесы, что позволяет каждому найти подходящий вариант.
    • Безопасность сделок – GotBee.ru гарантирует юридическую чистоту сделок и защиту интересов обеих сторон.
    • Прозрачность условий – все условия сделок четко прописаны, что исключает недопонимания и конфликты в будущем.
    • Быстрый процесс заключения договоров – благодаря удобному интерфейсу платформы, процесс оформления сделок занимает минимум времени.

    Биржа готового бизнеса GotBee.ru – это надежный помощник в поиске и покупке/продаже бизнеса. Благодаря широкому выбору предложений, безопасности сделок и прозрачности условий, платформа становится идеальным выбором для всех, кто стремится к успеху в мире предпринимательства.

  2. Сложите бесплатно собственное генеалогическое древо онлайн и сохраните его в онлайн

    [img]https://data0.gallery.ru/albums/gallery/3361637-691c2-132571706-m750x740-u376bc.jpg[/img]

    В этой заметке мы хотели бы представить пользователям неповторимый портал «Код памяти», который будет не только интересен, но и полезен буквально каждому! Он был запущен с целью помочь пользователям сделать и сохранить сведения о своих предках, составить древо [url=https://memorycode.ru/familytree]https://memorycode.ru/familytree[/url] изучить и сохранить историю собственной семьи. Данный портал открывает удобные возможности для занесения и сохранения информации о родственниках, размещения фотографий, документов и заметок о семейной истории.

    Код памяти – ресурс, какой будет полезен любому

    [img]https://data0.gallery.ru/albums/gallery/3361637-c9a09-132571708-m750x740-u8ab0f.jpg[/img]

    Сегодня достаточно многие люди хотели бы сделать генеалогическое древо и это не удивительно, потому что оно позволяет:

    • Узнать как можно больше о собственных предках. Получится отследить родословную до самых первых известных членов семейства.
    • Выяснить личную генетическую историю. Например, если вдруг имеются какие-либо патологии в семье, можно использовать данные из генеалогического древа для выявления риска возникновения этих заболеваний.
    • Построить связь с дальними родственниками. Это ведет к новым теплым взаимоотношениям и возможностям для обмена сведениями об общей истории.
    • Поддерживать культурное наследие. Можно применять сведения из генеалогического древа для формирования семейных обычаев и праздников, которые передаются от поколения к поколению.
    • Найти увлекательное увлечение. Формирование генеалогического древа может стать интересным и увлекательным делом.

    [img]https://data0.gallery.ru/albums/gallery/3361637-7b9ed-132571709-m750x740-u15dbc.jpg[/img]

    С привлечением портала memorycode.ru пользователи получают возможность отслеживать свои родственные связи, сохранять историю своей семьи для новых поколений, информационных источников о предках, а также общаться с остальными посетителями и делиться личными исследованиями и историями.

    Для составления генеалогического древа «Код памяти» становится наилучшим инструментом, потому что понятный интерфейс и функционал ресурса позволят запросто сохранить и систематизировать сведения о своих предках на 100% бесплатно! Кроме этого, на портале предлагается множество интересной информации, а еще советы по поиску и сохранению семейной истории, что помогает посетителям эффективно разрешать вопросы, которые связаны с генеалогией своей семьи.

  3. Заказать качественный бетон в Нижнем Новгороде, Купить качественные бетонные смеси в Нижнем Новгороде, по доступным ценам, Заказать качественный бетон у проверенных поставщиков в Нижнем Новгороде, Купить бетон на заводе в Нижнем Новгороде, Купить бетон оптом в Нижнем Новгороде: лучшие предложения, по выгодной цене, с гарантией качества, по доступной цене
    купить бетон в нижнем новгороде https://1beton-52.ru/ .

  4. Клининговая компания “Простая уборка” в Нижнем Новгороде. Цены на клининг на сайте: [url=https://prostaya-uborka1.ru]https://prostaya-uborka1.ru[/url]

  5. Клининговая компания “Простая уборка” в Нижнем Новгороде. Цены на клининг на сайте: [url=https://prostaya-uborka1.ru]https://prostaya-uborka1.ru[/url]

  6. You can now order your Spirit Halloween costume on Uber Eats
    kraken войти
    Uber is cashing in on spooky season.

    The ridehailing company will now deliver costumes, makeup, and even decorations from Spirit Halloween, the largest Halloween retailer in North America, Uber announced in a press release Friday.

    Big-box retailers are getting into Halloween earlier and earlier, suggesting American consumers continue spending on the October holiday even as they pull back from other discretionary purchases.

    Customers in the US and Canada can buy their Chipotle burrito costume for the same price as in store, but without having to visit the seasonal Halloween store that pops up in abandoned storefronts every year, Uber said. Of course, there will still be the fees associated with Uber Eats delivery.

    Spirit Halloween has 1,525 locations.

    “The holiday season officially kicks off this time of year, and households across the country are looking to on-demand delivery to get what’s needed—now,” said Beryl Sanders, director of US grocery and retail partnerships at Uber, in a statement.

    Since the pandemic, different types of retailers have partnered with Uber to deliver their goods – such as Olive Garden for its breadsticks and pasta. Uber Eats has also partnered with Big Lots, Lowe’s, Michael’s and Party City for on-demand delivery.

    Uber and its competitors have also experimented with robot deliveries, though those have not fully taken off in the US market.

  7. Клининговая компания “Простая уборка” в Нижнем Новгороде. Цены на клининг на сайте: [url=https://prostaya-uborka1.ru]https://prostaya-uborka1.ru[/url]

  8. Very hard to find casinos that accept Paypal deposits. Would be great if there were more casinos that accept it, there are few which I know like Paddypower and Williamhill but should be more! Withdraw from Paypal to bank account is quick, although it does not include weekends. I get the money same day if it’s processed… Most PayPal casino sites out there provide fast payouts. The average time depends on the online casino. Some of the new PayPal casinos have instant payouts. However, expect the period to range from a few minutes to up to three days, with most PayPal casinos taking up to 24 hours. Remember, it might take a little longer if you are making the first withdrawal and have not verified your account. You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.
    https://3rd-strike.com/author/rkysmarsosem1984/
    About: Do you love getting rewarded for trying out new free games? If so, AppStation should be your next download! AppStation is an entirely free app and only available for Android users. It lets you win real money by downloading and trying out new mobile games. This casino has many welcome bonus options, such as $7 Real money bonus, 1,000% Bonus on your 1st deposit, or a 277% online casino best deposit bonus that takes Cash App. There is also a classic offer which is available for 4 refills. Of course, all bonuses can be activated using the Cash App. We have to admit, the end-product of the supposed MrBeast Casino app looked convincing and we can’t blame all those people falling for the scam. The app looked real and fake accounts bragging they’ve won lots of money had an irresistible pull for those that like real money gambling.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

74 − seventy three =