BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  130 随笔 :: 2 文章 :: 150 评论 :: 0 Trackbacks


A Flat File Connection Manager will initially treat each column as a 50 character string by default. Leaving this default behavior will harm you when you go to an integer column into SQL Server or if your column contains more data than 50 characters. This Advanced page in the connection manager is the most important work you can do to ensure that all the data types for the columns are properly defined. You will also want to keep the data types as small as possible. If you have a zip code column for example that’s only 9 digits in length, define it as a 9 character string. Doing this will save an additional 41 bytes in memory multiplied by however many rows you have.

A frustrating point with SSIS sometimes is how it deals with SQL Server data types. For example, a varchar maps in SSIS to a string column. It was made this way to translate well into the .Net development world and to make an agnostic product. The below table contains some of the common SQL Server data types and what they’re going to map to in a Flat File Connection Manager.

SQL Server Data Type Flat File Connection Manager Data Type


eight-byte signed integer [DT_I8]
binary byte stream [DT_BYTES]
Bit Boolean [DT_BOOL]
Date single-byte unsigned integer [DT_UI1]
Datetime database timestamp [DT_DBTIMESTAMP]
Decimal numeric [DT_NUMERIC]
Float float [DT_R4]
Int four-byte signed integer [DT_I4]
Image image [DT_IMAGE]
Nvarchar or nchar Unicode string [DT_WSTR]

Unicode text stream [DT_NTEXT]

Numeric numeric [DT_NUMERIC]
Smallint two-byte signed integer [DT_I2]
Text text stream [DT_TEXT]
Timestamp byte stream [DT_BYTES]
Tinytint single-byte unsigned integer [DT_UI1]
Uniqueidentifier unique identifier [DT_GUID]
Varbinary byte stream [DT_BYTES]
Varchar or char string [DT_STR]
Xml Unicode string [DT_WSTR]
posted on 2008-10-24 10:15 渠上月 阅读(505) 评论(0)  编辑  收藏 所属分类: OLAP