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 |
|
bigint
|
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] |
| ntext |
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] |