To use a User Defined Function, you create a function with parameters. Executes the function with set parameters and uses this in your project. 

In my example I have a CSV file that is set up in a way so I cant use the normal single text file data source. It is set up like this.

DW_Id,Company;Business Posting Group;Business Posting Group Description;City;Country Code;Country Name;Customer Posting Group;Name;No;Salesperson Code;Salesperson Name;State;IncrementalTimeStamp;DWBatch;DWSourceCode;DWTimeStamp|1,Cronus International Ltd.;EXPORT;Other customers and vendors (not EU);Miami;US;USA;FOREIGN;Spotsmeyer's Furnishings;01121212;JR;John Roberts;FL;14-06-2018 09:42;25;Nav;14-06-2018 09:42|2,Cronus International Ltd.;EXPORT;Other customers and vendors (not EU);Chicago;US;USA;FOREIGN;Progressive Home Furnishings;01445544;JR;John Roberts;_;14-06-2018 09:42;25;Nav;14-06-2018 09:42|3,Cronus International Ltd.;EXPORT;Other customers and vendors (not EU);Atlanta;US;USA;FOREIGN;New Concepts Furniture;01454545;JR;John Roberts;GA;14-06-2018 09:42;25;Nav;14-06-2018 09:42|

It contains three different delimiters. , ; and |. There is a link to the file in the bottom of this.

I have added it to my project by making a custom table. that contains a single nvarchar(max) field. I then copied the data from the csv file into it as custom data.

What I want to do, is to create a SPLIT function, to do this work for me. I searched the web and found one that could be used.

CREATE FUNCTION [dbo].[fn_Split]
(
@String nvarchar(max), 
@Delimiter nvarchar(1)
) 
RETURNS @TempTable TABLE (whichitem int identity, items nvarchar(max)) 
as 
begin 
 
 declare @intPosition int
 ,@vchElement nvarchar(max)
 
 if len(@String) < 1 or @String is null
 return 
 
 set @intPosition = 1
 while @intPosition <> 0 
 begin 
 set @intPosition = charindex(@Delimiter, @String) 
 if @intPosition <> 0 
 set @vchElement = left(@String, @intPosition - 1) 
 else 
 set @vchElement = @String 
 
 if len(@vchElement) > 0
 insert into @TempTable(items) values(@vchElement) 
 
 set @String = right(@String, len(@String) - @intPosition) 
 if len(@String) = 0 break 
 end
 
 return
end 

It has two parameters, a text and a delimiter. What you receive is the data called items and a id called whichitem.

To add it to a User Defined Function you start by adding one to your project.

Then you can write your function, or copy and paste it in. You can use parameters and project variables, if necessary. Remember that the name of the function must be the same in the code and as the name itself.

To use the function you can make a insert into script action, a custom view, a stored procedure or a custom table insert.

I made a custom table containing the first two fields of the file. The ones with a , field delimiter and a |.

Here below is a insert into script action script that adds to that table.

DECLARE @String nvarchar(max) = (SELECT [CustData] FROM [Data Cust])

INSERT INTO [Split 1_R]
(
 [ID],
 [Data]
)
SELECT MAX(CASE WHEN f2.whichitem = 1 THEN f2.items END) AS [ID],
 MAX(CASE WHEN f2.whichitem = 2 THEN f2.items END) AS [Data]
FROM [fn_split](@String, '|') AS f1
CROSS APPLY [fn_split](f1.items, ',') AS f2
WHERE f1.whichitem <> 1
GROUP BY f1.whichitem

I start by declaring a @String variable and filling it with the data from my file. Then I change the CRLF delimiter to a |. Then I use the split function with the data and the | as delimiter, to get the rows. Then I use cross apply on the function with , as the delimiter, to get the columns. Finally I use the witchitem field to get the column id and its data. The where clause is to remove the header line and the Group By is to get it all on one row.

Here is how I use this in a parametrized script. Notice that I can use the user defined function as a parameter.

When you set this to run as a pre step before data cleansing and deploy/execute everything, you will get this.

So as you can see it still needs to be split out further. This is where the bonus of having it as a function comes in. You can just use it again.

Below is a custom view script that splits it a second time.

CREATE VIEW [dbo].[Split 2] AS
SELECT f1.[ID] AS 'DW_Id',
 MAX(CASE WHEN f2.whichitem = 1 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_Account',
 MAX(CASE WHEN f2.whichitem = 2 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Gen. Bus. Posting Group',
 MAX(CASE WHEN f2.whichitem = 3 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Business Posting Group',
 MAX(CASE WHEN f2.whichitem = 4 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'City',
 MAX(CASE WHEN f2.whichitem = 5 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Country/Region Code',
 MAX(CASE WHEN f2.whichitem = 6 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Country Name',
 MAX(CASE WHEN f2.whichitem = 7 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Customer Posting Group',
 MAX(CASE WHEN f2.whichitem = 8 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Name',
 MAX(CASE WHEN f2.whichitem = 9 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'No.',
 MAX(CASE WHEN f2.whichitem = 10 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Salesperson Code',
 MAX(CASE WHEN f2.whichitem = 11 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Salesperson Name',
 MAX(CASE WHEN f2.whichitem = 12 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'IncrementalTimeStamp',
 MAX(CASE WHEN f2.whichitem = 13 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_Batch',
 MAX(CASE WHEN f2.whichitem = 14 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_SourceCode',
 MAX(CASE WHEN f2.whichitem = 15 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_TimeStamp'
FROM [Split 1] AS f1
CROSS APPLY [fn_split](f1.[Data], ';') AS f2
GROUP BY f1.[ID]

This is more or less the same procedure as before. I pull the data from the Split 1 table and use cross apply and the split function on the data field to get the columns filled.

Here below is how it is looking in a parametrized custom view.

So in general with functions, you need to figure out what parameters you need and where you want to use them. Here below is the final result. As you can see it now resembles a normal table.