SSIS 经验总结

１、在Execute SQL Tasｋ组件中创建临时表，需要使用ｃｒｅａｔｅ显示创建，不能使用ｓｅｌｅｃｔ　ｉｎｔｏ。

 

２、Ｄｕｍｐ数据到ＳｑｌＳｅｒｖｅｒ中，存在数据文件情况下，Ｂｕｌｋ　Ｉｎｓｅｒｔ　Ｔａｓｋ组件快于Ｄａｔａ　Ｆｌｏｗ Task。在Ｄａｔａ Flow Task中，使用OLE DB Destination快于ＡＤＯ　ＮＥＴ　Ｄｅｓｔｉｎａｔｉｏｎ，设置ＯＬＥ数据访问模式为ｆａｓｔ。

如：

 

３、使用Bulk Insert Task直接从源ＤＢ导出数据到目标ＤＢ，需要先使用Ｄａｔａ Flow Task将数据导入指定文件中。虽然Bulk速度很快，但是数据导入文件的速度受ＩＯ限制，所以当数据文件不存在情况下使用Bulk并不理想。

 

4、Lookup is a case sensitive and blank space sensitive.
So need trim the lookup column data and use either UPPER/LOWER case for comparison.

 

5、数据类型对照

SSIS->DB

double-precision float [DT_R8]->FLOAT

string [DT_STR]->CHAR

four-byte signed integer [DT_I4]->INT

database timestamp [DT_DBTIMESTAMP]->DATETIME

 

6、Error:The multi-part identifier could not be bound.

解决：不使用alias别名，直接使用表名应用列

 

7、大批量数据的更新方法和速度

1）在Data Flow Task中使用OLE DB Command直接更新

具体做法可参见：http://blog.csdn.net/zjcxc/article/details/1202876

 

2)先使用Execute SQL Task 创建临时表，然后使用Data Flow Task数据导入临时表，最后使用Execute SQL Task 执行Update 语句，根据临时表更新目标表

具体做法可参见：http://bidn.com/blogs/DanMatisis/ssas/689/ssis-updating-a-table-with-a-temp-table-not-an-ole-db-command

 

3）在数据库中创建物理表，使用Data Flow Task数据导入物理表，最后使用Execute SQL Task 执行Update 语句，根据物理表更新目标表

具体做法可参见：http://www.ssistalk.com/2009/10/29/ssis-why-is-my-data-flow-so-slow-with-an-ole-db-command-component/

 

速度1<2<3

原理：

1）OLE DB Command是逐条更新，每条数据的更新都需要扫描全表，慢于后两种方法的批量数据更新

2）数据导入物理表可以使用fast load（参考上面第2条） 方式，速度快于导入临时表

 

注意：

如果使用第2种方法，必须将目标数据源的Connection的属性RetainSameConnection设置成true。这样视为保证在Execute SQL Task 中创建的临时表可以继续在Data Flow Task中使用。大家都知道数据库一个连接一旦关闭，这个连接所创建的临时表也就消失，就是这个原因。

在Data Flow Task中，使用OLE DB Destination将数据导入临时表时，必须将OLE DB Destination的属性ValidateExtranalMetadata设置成false。因为在package运行之前，临时表是不存在的，所以一旦验证package就会失败。

在OLE DB Destination中使用New按钮创建临时表时，临时表名需要使用[]括起来。如Create table [##Temptable]...

 

8、like关键字的替代方法

Column LIKE '%abc%'

SSIS Expression:

FINDSTRING([Column],"abc",1)>0

 

Column LIKE 'abc%'

SSIS Expression:
SUBSTRING([ Column],1,3) == "abc"

or

LEFT( [ Column] , X) == "value"

or

FINDSTRING([Column],"abc",1)==1

 

Column LIKE '%abc'

SSIS Expression:

RIGHT( [Column] , X) == "value"

or

LEFT(REVERSE([Column] ), X) == "value"

 

9、case when 关键字的替代方法

case when column = "a" then 1

when column = "b" then 2

else 3 end

<Expression>

column == "a"?1:column == "b"?2:3

 

10、error:cannot convert between unicode and non-unicode string data types.

Resolve:convert the column data type to DT_STR (If you know the size)/ DT_TEXT  in the data convertion transformation.

 

11、Convert Blank/Empty into NULL use Derived Column

1- set the DEFAULT(NULL) for EVERY column that needs this behaviour

2-set up some Derived Column option in the package to return NULL if the value is missing.

 

TRIM(<YourColumnName>) == "" ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : <YourColumnName>

 

FYI：http://www.bidn.com/blogs/DonnyJohns/ssas/1919/handling-null-or-implied-null-values-in-an-ssis-derived-column