ssis面试题_SSIS面试问题

ssis面试题

These SSIS interview questions cover many of the top topics that you will be asked when applying for your next SSIS developer job. Each question has links to articles that cover the topic in depth.

这些SSIS面试问题涵盖了许多在申请下一个SSIS开发人员工作时会被问到的重要话题。每个问题都有指向深入讨论该主题的文章的链接。

介绍 (Introduction)

Looking for a job related to SQL Server Integration Services (SSIS)? An SSIS developer in USA has an average salary of 78,414 USD, which is a good salary compared of other related jobs according the PayScale website:

寻找与SQL Server集成服务（SSIS）相关的工作？美国的SSIS开发人员的平均薪水为78,414美元，与PayScale 网站上的其他相关工作相比，是不错的薪水：

Job	Salary per year (USD)
SSIS Developer	78,414
Java Developer	73,285
DBA	71,775
C++ Developer	69,948
C# Developer	68,535
PHP Developer	62,819

工作	年薪（美元）
SSIS开发人员	78,414
Java开发人员	73,285
数据库管理员	71,775
C ++开发人员	69,948
C＃开发人员	68,535
PHP开发人员	62,819

The question is. How can I get a job as an SSIS developer?

问题是。如何获得SSIS开发人员的工作？

In this article, we will talk about interview questions to learn SSIS for newbies and also for experienced users and have enough knowledge to pass the interview questions and get a nice job.

在本文中，我们将讨论面试问题，以便为新手和有经验的用户学习SSIS，并具有足够的知识通过面试问题并获得出色的工作。

SSIS认证考试 (Certification exams for SSIS)

There are official certification exams from Microsoft to be an expert in SSIS. The following certifications exams can be useful to validate your knowledge and if you do not have the knowledge, you can look for the courses, books, training material related to these exams:

Microsoft拥有官方认证考试，可以成为SSIS的专家。以下认证考试对于验证您的知识很有用，如果您不具备这些知识，则可以查找与这些考试相关的课程，书籍和培训材料：

These exams not only cover SSIS, but other areas like SSAS and SSRS. The skills required to pass these exams are deep.

这些考试不仅涵盖SSIS，而且涵盖SSAS和SSRS等其他领域。通过这些考试所需的技能很深。

If you pass these exams, you can pass any interview question for sure. They are difficult exams that requires a lot of experience.

如果您通过了这些考试，则可以肯定地通过任何面试问题。他们是困难的考试，需要大量的经验。

SSIS面试问题 (SSIS Interview questions)

您以前在SSIS方面的经验是什么？ (What is your previous experience in SSIS?)

The interviewer will ask you what type of SSIS jobs did you do in your previous jobs. If you do not have any experience, you must be honest. However, try to do some labs and experiments on your own. If your company does not require to use all the SSIS components, try to learn by yourself as much as possible in your free time before the interview.

面试官会问您在以前的工作中您从事过什么类型的SSIS工作。如果您没有任何经验，则必须诚实。但是，尝试自己做一些实验。如果您的公司不需要使用所有SSIS组件，请尝试在面试之前的空闲时间中尽可能多地学习。

You can say, that your SSIS tasks were simple, but you have enough experience about performance, parallel tasks and scripting. Try to read as much as possible to have great skills.

您可以说，您的SSIS任务很简单，但是您对性能，并行任务和脚本有足够的经验。尝试阅读尽可能多的技巧。

什么是SSIS程序包？ (What is an SSIS package?)

Is the storage file with .dtsx extension that contains your control flow, data flow, connections, variables, parameters, event handlers, etc in SSIS projects.

是具有.dtsx扩展名的存储文件，它包含SSIS项目中的控制流，数据流，连接，变量，参数，事件处理程序等。

Here you have the documentation related:

这里有相关的文档：

Integration Services (SSIS) Packages
集成服务（SSIS）软件包

什么是控制流程？ ( What is the control flow?)

The control flow is part of the package and contains tasks with functionality (create backups, execute scripts, execute SQL tasks, connect to FTP, etc.) and containers (can be sequential, for each loop, for loops) and finally it includes constraints to join flows.

控制流是程序包的一部分，包含具有功能的任务（创建备份，执行脚本，执行SQL任务，连接到FTP等）和容器（可以是顺序的，对于每个循环，对于循环），最后包括约束加入流程。

For more information about the control flow, refer to this link:

有关控制流的更多信息，请参考以下链接：

Control Flow
控制流

什么是数据流？ (What is a data flow? )

The data flow allows to export data from different sources to different destinations and transform the data if necessary. There is a Data Flow component in the control flow and when you double click the task you have new tasks to import and export data.

数据流允许将数据从不同的源导出到不同的目的地，并在必要时转换数据。控制流中有一个数据流组件，当您双击任务时，您就有新的任务来导入和导出数据。

For more information about the data flow, refer to this link:

有关数据流的更多信息，请参考以下链接：

Data Flow Task
数据流任务

什么是SSIS目录？ (What is an SSIS Catalog?)

The SSIS catalog is a database to store all the deployed packages. It is used for security reasons to store and handle the deployed packages.

SSIS目录是一个数据库，用于存储所有已部署的软件包。出于安全原因，它用于存储和处理已部署的软件包。

Here you have the theory related to the SSIS Catalog:

在这里，您具有与SSIS目录有关的理论：

如何在SSIS目录中部署程序包？ (How can you deploy a package in the SSIS Catalog?)

In the SSIS catalog you can deploy your packages. The packages can be stored there as a centralized storage database.

在SSIS目录中，您可以部署程序包。软件包可以作为集中存储数据库存储在此处。

Here you have the links about package deployment in the SSIS Catalog:

在这里，您可以在SSIS目录中找到有关程序包部署的链接：

什么是脚本任务，什么是脚本组件？两者有什么区别？ (What is the script task and what is the script component? What is the difference between the two?)

The first one is used to program any task in the control flow using C# or VB. The Script Component is in the Data Flow and it is used to transform columns in source and/or destination.

第一个用于使用C＃或VB对控制流中的任何任务进行编程。脚本组件位于数据流中，用于转换源和/或目标中的列。

I know experienced SSIS developers who never programmed a Script. I strongly recommend you to learn C# or VB to increase your capabilities in SSIS. The script task and script component will save you in many scenarios.

我认识经验丰富的SSIS开发人员，他们从未编写过脚本。我强烈建议您学习C＃或VB，以增强SSIS的功能。脚本任务和脚本组件将在许多情况下为您省钱。

For more information about the script task and the script component refer to these links:

有关脚本任务和脚本组件的更多信息，请参考以下链接：

您何时使用脚本组件以及何时使用派生列？ (When do you use the Script component and when the derived column?)

In general, use the derived column if the transformation is simple and the Script component if it is complex.

通常，如果转换很简单，则使用派生列；如果复杂，则使用Script组件。

For more information, refer to this link:

有关更多信息，请参考以下链接：

SSIS Script component vs derived column
SSIS脚本组件与派生列

什么是SSIS表达式？ (What are the SSIS Expressions?)

If you are not familiar with SSIS Expressions, it, most likely means that you are not an expert and your SSIS experience is basic, so it is important to become very familiar with this feature. SSIS Expressions can be used to filter information, to work with parameters, concatenate information, create conditions. In other words, it helps to work with dynamic connections, conditions and work with loops.

如果您不熟悉SSIS表达式，则很可能意味着您不是专家，并且您的SSIS经验很基础，因此非常熟悉此功能非常重要。 SSIS表达式可用于过滤信息，使用参数，连接信息，创建条件。换句话说，它有助于处理动态连接，条件以及处理循环。

For more information about SSIS Expressions, refer to these links:

有关SSIS表达式的更多信息，请参考以下链接：

您如何停止永远运行的软件包？ (How would you stop a package that is running forever?)

It depends. If you are running the package in the SQL Agent, you can kill the process using T-SQL. Alternatively, if the package is running in the SSIS catalog it can be stopped using Active Operations window or the stop operation stored procedure.

这取决于。如果在SQL Agent中运行程序包，则可以使用T-SQL终止进程。或者，如果程序包在SSIS目录中运行，则可以使用“活动操作”窗口或停止操作存储过程将其停止。

For more detailed information, refer to our article related:

有关更多详细信息，请参阅我们的相关文章：

How to stop a runaway SSIS package
如何停止失控的SSIS程序包

如何检索存储在MSDB数据库中的软件包的数据？ (How can you retrieve data of packages stored in the MSDB Database?)

The packages can be stored, back up in the system database MSDB. You have system tables to get data from them. For example, the dbo.sysssispackages can give you the list of SSIS packages stored in the database. For more detailed information about the structure, msdb tables, please refer to our article related:

程序包可以存储，备份在系统数据库MSDB中。您有系统表可以从中获取数据。例如，dbo.sysssis软件包可以为您提供存储在数据库中的SSIS软件包的列表。有关该结构，msdb表的更多详细信息，请参阅相关的文章：

How to retrieve information about SSIS packages stored in MSDB Database
如何检索有关存储在MSDB数据库中的SSIS包的信息

您将如何监视SSIS的性能？ (How would you monitor performance in SSIS?)

Microsoft includes performance counters to monitor Applications. SSIS is not the exception and it includes performance counter to measure the packages executed, Blob read/write, buffer information, rows read/write and more.

Microsoft包括性能计数器以监视应用程序。 SSIS也不例外，它包括性能计数器以衡量执行的程序包，Blob读/写，缓冲区信息，行读/写等等。

For more information about this topic, refer to our link:

有关此主题的更多信息，请参考我们的链接：

SSIS Pipeline performance counters guide
SSIS管道性能计数器指南

Note: For a SSIS performance monitoring tool, check out Foglight

注意：有关SSIS性能监视工具的信息，请查看Foglight

您是否有将Azure连接到SSIS的经验？ (Do you have experience connecting Azure to SSIS?)

Microsoft offers a free Azure Feature Pack to connect to Azure. Also, other companies offer plugins for Azure to connect.

Microsoft提供了免费的Azure功能包以连接到Azure。此外，其他公司也提供了用于Azure连接的插件。

The Azure Feature Pack includes task and connectors to connect to the Azure Blob Storage, Azure Data Lake, HDInsight.

Azure功能包包括任务和连接器，用于连接到Azure Blob存储，Azure数据湖，HDInsight。

If you are not familiar with Azure, we recommend to read these articles related:

如果您不熟悉Azure，建议阅读以下相关文章：

你熟悉^第三方插件SSIS？ (Are you familiar with 3^rd party SSIS plugins?)

In many cases, the default SSIS tools are enough, but in many cases, you will need to install plugins to increase to functionality to connect to Amazon, Facebook, Gmail and thousands of other sources.

在许多情况下，默认的SSIS工具就足够了，但在许多情况下，您将需要安装插件以增加功能以连接到Amazon，Facebook，Gmail和数千个其他来源。

There are several companies that created some extra tasks and connectors that may be useful.

有几家公司创建了一些可能有用的额外任务和连接器。

I strongly recommend you to take a look to the plugins of ZappySys, CozyRoc, KingWaySoft or other companies. They offer interesting plugins to connect to REST API, XML, JSON, Amazon and thousand other sources using tasks.

我强烈建议您看一下ZappySys，CozyRoc，KingWaySoft或其他公司的插件。他们提供了有趣的插件，可使用任务连接到REST API，XML，JSON，Amazon和数千种其他来源。

您知道如何编写自己的SSIS任务吗？ (Do you know how to program your own SSIS task?)

If you can create your own custom classes, it means that you are a really experienced user. If you have great programming skills in C# or VB, it is possible to create your own task.

如果您可以创建自己的自定义类，则意味着您是一个真正有经验的用户。如果您具有C＃或VB的出色编程技能，则可以创建自己的任务。

For more information about creating your own custom task, refer to this link:

有关创建自己的自定义任务的更多信息，请参考以下链接：

Developing a Custom Task
开发自定义任务

是否可以在Visual Studio中使用SSIS对象并在没有SSIS的情况下提取数据？ (Is it possible to use SSIS objects in Visual Studio and extract data without SSIS?)

Yes, it is. You can have a Visual Studio project in C# like a web application a Windows desktop application, a console application and invoke SSIS objects to program tasks without SSIS projects. In other words, you could create your control flows, data flows, connections using just C# code or Visual Studio code. In most of the cases, it is not practical to do it, because it may take longer time. However, it can be necessary if the task in the SSIS project has some functionality limitation that could be covered by the code in C#.

是的。您可以在C＃中拥有Visual Studio项目，例如Web应用程序，Windows桌面应用程序，控制台应用程序，并调用SSIS对象来对没有SSIS项目的任务进行编程。换句话说，您可以仅使用C＃代码或Visual Studio代码创建控制流，数据流，连接。在大多数情况下，这样做是不切实际的，因为这可能需要更长的时间。但是，如果SSIS项目中的任务具有某些功能限制（可能会被C＃中的代码覆盖），则可能是必要的。

If you already worked with the SSIS classes, it means that you are a Senior SSIS developer.

如果您已经使用过SSIS类，则意味着您是高级SSIS开发人员。

For more information about this topic, refer to these links:

有关此主题的更多信息，请参考以下链接：

您在SSIS中是否存在性能问题。如果是这样，您如何解决问题？ (Did you have performance problems in SSIS. If so, how did you solve the problem?)

A typical problem with performance in SSIS is the buffer; the out of memory error. To solve performance problems, we strongly recommend you to read the article: Integration Services: Performance Tuning Techniques white paper.

SSIS中性能的一个典型问题是缓冲区。内存不足错误。为了解决性能问题，强烈建议您阅读以下文章： Integration Services：性能调优技术白皮书。

结论 (Conclusions)

In this article, we learned how some important SSIS interview questions that may be asked in a job interview for an SSIS position.

在本文中，我们了解了在工作面试中可能会针对SSIS职位提出的一些重要的SSIS面试问题。

It is important to know that you will never know everything. Even the SSIS creators do not know everything, but it is important to learn each day more to be in the top of list to score a job in SSIS.

重要的是要知道您将一无所知。甚至SSIS的创建者也不是一无所知，但是重要的是每天多学习一点，使其成为SSIS的一份重要工作。

翻译自: https://www.sqlshack.com/ssis-interview-questions/