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 ...