使用SSIS从SQL Server的Ne​​tezza增量负载

问题描述

我正在尝试使用SSIS从Sql Server 2008到Netezza(Nps6)进行增量加载.使用了Netezza 5.x版本的OLEDB驱动程序.我在Maximum insert commit size = 0中使用Table or View - Fast Load选项.

I am trying to do a incremental load from Sql server 2008 to Netezza (Nps6) using SSIS.Netezza 5.x version OLEDB driver used. I am using Table or View - Fast Load option with Maximum insert commit size = 0.

在这里，我试图将几千条记录插入到Netezza表中.此目标表包含数百万条记录.此数据流任务需要一个小时才能完成.当我查看Netezza Administrator活动查询时，我发现问题如下，

Here I am trying to insert few thousands of records to a Netezza table. This destination table contains millions of records. This Data flow task was taking a hours to complete. When I looked into the Netezza Administrator Active Queries I could see that a query like below was the problem,

SELECT * FROM Destination_Table;

下一步是如下所示的外部表加载

The next step is an external table load like below,

insert into "destination_table"(col1, col2, col3)
select c0, c1, c2 from external '/dev/null' (c0, c1, c2) using (
    remotesource odbc' delimiter ' ' escapechar '\' ctrlchars 'yes' crinstring 'yes' timeroundnanos 'yes' encoding 'internal' maxerrors 1
) ;

任何人都可以帮助我理解为什么需要SELECT * FROM目标表进行加载.或Netezza OLEDB驱动程序如何与SSIS一起使用.

Can anyone help me understand why a SELECT * FROM the Destination Table is required for load. Or how a Netezza OLEDB driver works with SSIS.

感谢您的帮助.

推荐答案

在安装OLEDB 6.x版本后，不会发生此"SELECT * FROM DESTINATION TABLE"问题.我可以看到OLEDB 6版本的性能有了很好的改进.但是，如果我们正在开发OLEDB 5.x版本，我认为最好先加载到阶段表，然后再加载到目标表

After installing OLEDB 6.x version this "SELECT * FROM DESTINATION TABLE" issue is not occurring. I could see a good performance improvement with OLEDB 6 version. But, If we are working on OLEDB 5.x version, i believe it is better to load to a stage table and then load to the destination table

这篇关于使用SSIS从SQL Server的Ne​​tezza增量负载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！