Sql-server – SSIS Script to split string into columns

On line 9, instead of GetType(Byte) you need GetType(Byte()).

Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.

If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the encoding (or convert the single byte to a byte array of length 1 and use common encoding code).

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.

Usage

In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.

Instead of

SELECT
    E.BusinessEntityID
,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
FROM
    HumanResources.Employee AS E

use a query such as

SELECT
    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
FROM
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.

Benefits

• Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
• No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
• No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...