The Script Component as a Destination
Writing data at non-ordinal destinations and in special formats creates a need for some custom destinations in the Data Flow. The Script Component as Destination will help SSIS Developers get rid of such problems.
In this recipe, we read some data rows from a database query and load them into a text file destination in a specific format as shown here:
NationalIDNumber FirstName|LastName|Gender|MaritalStatus JobTitle;HireDate;DepartmentName;DepartmentGroupName;StartDate;EndDate
The first data row should have NationalIDNumber, the second data row should have personal information that's delimited by a vertical line ( | ), and finally the third row has professional information delimited by a semi colon ( ; ). Each data stream row will be separated into three output rows with different formatting. The regular Flat File Destination cannot handle this format of output, so we handle this problem using a Script Component as Destination.
How to do it...
Create a new...
