SSIS – Using a Script Component as a Source

Just a quick tutorial on using a script component as a source… Follow the link for screenshots.

First things first. Add a script component to the data flow. When prompted, select “Source” as the component type. (click on images for full sized version)

SSIS - Script Source Component 1

Next, for clarity’s sake, I’ll rename the default output. (An output simply contains a set of columns and will correlate to the green arrow on the data flow. The more outputs you have, the more green arrows you can use.) I’ll rename the default output, “Output 0” to “MyOutput.”

SSIS - Script Source Component 2

Next, I’ll add an output column called, “MyColumn” and its type will be an integer. Be sure to change the type accordingly.

SSIS - Script Source Component 3

From here, we can edit the script by clicking on the script section on the left of the above window. From there click on the “Design Script…” button. Below is the setup:

SSIS - Script Source Component 4

Notice how when I type, I am automatically prompted for a list of available items to choose from. One of them is the column I added previously. Let’s pick the column and assign a value of 123 to it. Full script below:

For SSIS 2005:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
        MyOutputBuffer.AddRow()
        MyOutputBuffer.MyColumn = 123
    End Sub

End Class

For SSIS 2008, it’s a bit different:
Visual Basic:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
        Output0Buffer.AddRow()
        Output0Buffer.Column = 123
    End Sub

End Class

C#:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

  public override void CreateNewOutputRows()
    {
        Output0Buffer.AddRow();
        Output0Buffer.Column = 123;
    }

}

You can close the script and then click OK on the script component dialog box. Now when you hook this source up to another component, you'll end up with one column with one row with a value of 123.

54 thoughts on “SSIS – Using a Script Component as a Source

  • Nisha

    Hi,

    I need to load data from SQL table into Oracle Table.In that one of the column should be encrypted and stored in oracle table.How to achieve this using SSIS.I have successfully transferred the data from SQL to Oracle table.But now the requirement is, i have to encrypt a column on the fly(i.e, encrypted value should be stored at the oracle side).
    Can someone help me on this ?

    Any help appreciated!!

    Thanks
    Nisha.V

  • Pingback: Reading object variable values in SSIS script component source | Zeidan Answers

  • Mandar Morekar

    You can try SQL Loader for transferring data to Oracle from SSIS.

    Create Data file using SSIS (File component, Script task etc)
    Encrypt data you want using control file and load data in Oracle.

    Control files are easy to maintain as well and SQLLoader will provide you with very good performance.

  • Rishi

    Hi,

    Very good article.
    I need to generate records with random values manually for testing purposes. I wish to automate this by creating a custom component to generate sample records containing random values. The component would have a UI where the user can specify the required column-names, the data-types and the no. of records required. Finally it must output these rows accordingly to be consumed by downstream components.

    What is a good approach to accomplish this ? Would a Script Task as a Source be augmented with a UI for this purpose ?

Comments are closed.