Using Script Component Transformation as SQL Server Function derived column

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply