Incorrect External column length in SSIS OLE DB Destination

Question

I am writing a set of data to a SQL Server 2008 table in SSIS (Visual Studio 2008). As I map my data set to a OLE DB destination I get a truncation error because the OLE DB destination columns have length 20. This happens for every column but I'll only show a sample of one column below.

This is the column as in the SQL table definition:

Column_name     Type        Length
FLAGVOEMPLOYEE  nvarchar    40

This is the metadata (checked in the data path editor) of the column I am passing to the destination as input:

Name            Data Type   Length
FlagVoEmployee  DT_WSTR     40

This is the column properties in Advanced Editor > Input and Output Properties > OLE DB Destination Input > External Columns:

Name            DataType                    Length
FLAGVOEMPLOYEE  Unicode string [DT_WSTR]    20

I tried:

• Manually changing the length attribute, but it will restore automatically back to 20
• Deleting the path and creating it again
• Deleting the OLE DB Destination and creating it again
• Changing Data access mode to normal or fast load
• Unmapping the column and remapping it

I am lost as I don't know how to change any other property that might help me avoid the truncation

Answer 1

To force it to provide a 40 character column, use the derived column transformation and modify your expression to this:

(DT_WSTR, 40)[FLAGVOEMPLOYEE]

I hope this helps and if it did please mark my answer so that it can benefit others as well!

Answer 2

Did you create the SSIS portion first and then change the Size of the column in the SQL table after? If so, you might be able to just delete and recreate the OLE DB Source to reset the metadata.

I also found this link: http://www.sqlservercentral.com/Forums/Topic927634-364-1.aspx

When working with SSIS and source or destination metadata changes SSIS does not reset the metadata it deletes the changed column and adds a new one at the end of the list. This results in messy packages after several changes have occured.

To reset the metadata on data sources, change the source to a table or query that has no matching column names and then view columns in the data source. This will delete and recreate the metadata. Then swith the source back to the changed source you are trying to refresh metadata for and view columns again. This will completely recreate the metadata for the source.

For destinations, change the destination to one with no matching column names and then view mapped columns. This will delete and recreate the metadata. Then switch back to the new or changed destination and view mapped columns. This effectively resets the metadata.