SSIS- OleDb快速加载与批量插入任务 [英] SSIS- OleDb Fast Load vs. Bulk Insert Task

问题描述

我已经在这个论坛上进行了包括线程在内的研究，但似乎找不到答案.

I have done research including threads on this forum but cant seem to find an answer.

我正在加载40列文本文件.目前没有任何转换.有8个文件〜25MB，共有1,400,000行.使用批量插入任务，加载将在3分钟内完成.使用OleDb目标和平面文件输入连接管理器，加载将在30分钟内完成.

I am loading text files with 40 columns. No transformation at this time. There are 8 files ~25MB with a total of 1,400,000 rows. Using Bulk Insert task the load completes in 3 minutes. Using OleDb destination and flat file input connection manager the load completes in 30 minutes.

据我了解，SSIS应该在OleDb连接后面使用批量插入.如果是这样，为什么会有如此巨大的差异?我一定做错了什么主意吗?

From all I have read, SSIS should be using Bulk Inserts behind the OleDb connection. If so, why is there such a dramatic difference? I must be doing something wrong, any ideas?

使用所有默认连接.表或视图快速加载.每批空白行，最大提交大小为2,147,483,674.我正在使用Sql2016，但在Sql2014中进行测试的结果类似.

Using all defaults for the connection. Table or View fast load. Blank rows per batch and max commit size 2,147,483,674. I am using Sql2016 but have had similar results testing with Sql2014.

推荐答案

您可能未设置数据流任务的属性，例如DefaultBufferSize和DefaultBufferMaxRows.

You might not setting up the properties of Data Flow Task such as DefaultBufferSize and DefaultBufferMaxRows.

这篇关于SSIS- OleDb快速加载与批量插入任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！