Using Script Component Transformation as SQL Server Function derived column

Did you tried to create a derived column in SSIS with a written sql server function before?

This is impossible with Derived Column Transformation because you can not use sql server functions in this transformation.

Solution:

Use Script Component Transformation.

this is a simple example :

1-create a sql server table with 2 fields named f1,f2

2-create a excel file with 3 column headers : f1,f2,fnew ( fnew is
the column with the value must be fetched from a Sql Server function)

3-suppose you have a sql server function that get a varchar and
concat it with special text and then result it as output, I named this
as SampleFunction

then create an SSIS package:

1-add a Data Flow

2-add an Ole db source to your sql server table

3-add a Script Component Transformation

4-use f1 and f2 as input columns, create 3 output columns as : f1out,f2out,fnew

5-create an ADO.NET connection manager to database which has your
sql server function in it (This is very important that you create
ADO.NET connection manager ,don’t create others because you will get
error of casting conversion then)

6- write codes below in your script component :

System.Data.SqlClient.SqlConnection cn;
    IDTSConnectionManager100 cnManager;
    System.Data.SqlClient.SqlCommand cmd;
    System.Data.SqlClient.SqlParameter param;

    public override void PreExecute()
    {
        cmd = new System.Data.SqlClient.SqlCommand("select dbo.SampleFunction(@var) as ot", cn);
        param = new System.Data.SqlClient.SqlParameter("@var", SqlDbType.VarChar);
        cmd.Parameters.Add(param);
    }

    public override void AcquireConnections(object Transaction)
    {
        cnManager =base.Connections.myCN;
        cn = (SqlConnection)cnManager.AcquireConnection(null);
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.f1out = Row.f1;
        Row.f2out = Row.f2;
        System.Data.SqlClient.SqlDataReader dtr;
        cmd.Parameters["@var"].Value = Row.f1;
        dtr = cmd.ExecuteReader();
        if (dtr.Read())
        {
            Row.fNew = dtr["ot"].ToString();
        }
        dtr.Close();
    }

    public override void ReleaseConnections()
    {
        cnManager.ReleaseConnection(cn);
    }

7-add excel destination

8-Run the SSIS package,the picture showed plan

9-Result:

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.