Menu
Whats the best way to convert DT_NTEXT to DT_WSTR in Derived Colum Transformation.
Regards
792
Since the column is a VARCHAR(MAX) data type in SQL Server, SSIS treats this as a DT_TEXT datatype. The DT_TEXT and DT_NTEXT are text data types and there are several limitations related to handling these datatypes in SSIS. For example, none of the string functions would work with these data types.
The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column. For example, a package can extract data from multiple sources, and then use this transformation to convert columns to the data type required by the destination data store.
You can also use the Derived Column transformation to convert data. This transformation is also pretty simple, you select an input column and then chose whether to replace this column or create a new output column. Then you need to apply an expression in order to come up with the new data for the output column.
The problem here is that the max length of DT_NTEXT is 2^30 ~ 1.073 billion characters, while DT_WSTR max length is 4000 characters.
If all your fields are less than 4000, try Data Conversion Transformation and it may only raise a warning at design time. You would add this before of after the Derived Column Transformation.
170
Below are two solution for any types of data conversion in SSIS Package
Use below link
SQL Server and SSIS Data Types
Mmanually convert that into Unicode string.
(DT_WSTR,20)Columnname expression.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
