Why does SSIS Lookup task output a DT_TEXT type even though the value returned through the lookup is a VARCHAR(50)?

Question

Running SQL Server Integration Services (64-bit install of SQL Server 2008 Developer) on a 64 bit version of Windows 7 Professional.

I have a simple SSIS package which imports data from a delimited text file, does a lookup to a SQL Server table, joining on a key field (int) and returns the UserName (varchar(50)) from that table.

Sample of import file data:

Key    Age
2      21
3      87
1      54

Key and age are both int.

Sample of lookup table:

Key    UserName
1      Joe
2      Tom
3      Paul
4      Jack
5      Mary

Key is int and UserID is varchar(50).

When I connect the Lookup box to a next step and connect them together (matching fields), the data type for the UserName field is DT_TEXT.

Why does this happen, and what's my best approach to fix or work around? I tried a Data Conversion component but SSIS didn't complained about converting DT_TEXT to varchar (DT_STR).

Answer 1

Have you tried using the "Show Advanced Editor..." (right click on the data flow source) and changing the Data Type Propery in the Output column in the "Input and Outputs" tab to data type "string [DT_STR]" of "Length 50" ?