Calling Stored Procedures

You can execute stored procedures in a source component as well as in script components.

Call Stored Procedures from a Source Component

Double-click the component in the Data Flow task to open the editor. In the "Data access mode" menu, select "SQL command" and enter the stored procedure. The support for stored procedure statements follows the standard form, shown below:

EXECUTE my_proc @first = 1, @second = 2, @third = 3;

EXEC my_proc @first = 1, @second = 2, @third = 3;

You can then use the SSIS Expression Builder to access the SQL statement that the source component executes at run time. To open the SSIS Expression Builder, click the Control Flow tab in SSIS Designer and in the Properties pane click the button in the box for the Expressions property. In the resulting Property Expressions Editor, click an empty row in the Property box and select the SQLStatement property of the RSSBus NetSuite source component from the drop-down menu. Then click the button in the row you just added. This displays the Expression Builder. In the Expression box, you can create new SQL commands that use the variables available at run time. Ensure that you enclose the expression in quotes.

Call Stored Procedures from a Script Component

You can follow the procedure below to call stored procedures in a script component in your SSIS Data Flow task. There are three types of script components: source, destination, and transformation. Select the type of script component suitable for your task and add it to the data flow. A source script component will have only outputs; a destination script component will accept only inputs; a transformation script component will accept input columns and produce output columns.

Before Editing the Script

After adding the component to the data flow, configure all the input and output columns in the Inputs and Outputs tab. Be sure to set the proper data type for each output, which can be found under the Data Type Properties in the right-hand column. Once all the columns are set, you can return to the Script tab and set any ReadOnlyVariables or ReadWriteVariables to use SSIS package variables. Note that the ReadWriteVariables can only be set in the PostExecute method in the script.

Finally, select the language you wish to code with: either C# or Visual Basic. Now you are ready to begin editing the script. Click the Edit Script button to begin. The example code below shows a typical stored procedure call.

After Editing the Script

After editing the code check if there are any errors in the Error List and resolve them as needed. Once the script is saved, you can close the window and click OK to exit the Script Transformation Editor. The script component is now configured and ready to use.

Example Script

The example below shows how to call the GetRoles stored procedure using a script component. You will need to add a reference to the RSSBus.SSIS20XX.NetSuite.dll (where 20XX is the version of SQL Server), which can be found in the lib subfolder of the installation folder. The example below shows how execute a stored procedure statement:

using System.Data.RSSBus.NetSuite;
...
public override void CreateNewOutputRows()
{
  string connectionString = "Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Offline=false;";
  using (NetSuiteConnection connection = new NetSuiteConnection(connectionString)) {
    NetSuiteCommand cmd = new NetSuiteCommand("GetRoles", connection);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add(new NetSuiteParameter("@Account_Name", "Checking"));
    // Add other parameters as needed ...

    NetSuiteDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read()) {
      Output0Buffer.AddRow();
      //Add output columns as necessary, for example:
      //Output0Buffer.Name = rdr["Name"].ToString();
      //...
      Console.WriteLine();
    }
  }
}

public override void PostExecute()
{
  //If you want to set any package variables, it must be done in this function
  //You will need to have already added these ReadWriteVariables in the Script Editor
  //For example:
  Variables.Success = true;
}

VB.NET

Imports System.Data.RSSBus.NetSuite
...
Public Overrides Sub CreateNewOutputRows()
  Dim connectionString As String = "Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Offline=false;"
  Using connection As New NetSuiteConnection(connectionString)
    Dim cmd As New NetSuiteCommand("GetRoles", connection)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New NetSuiteParameter("@Account_Name", "Checking"))
    ' Add other parameters as needed ...

    Dim rdr As NetSuiteDataReader = cmd.ExecuteReader()
    While rdr.Read()
      Output0Buffer.AddRow()
      'Add output columns as necessary, for example:
      'Output0Buffer.Name = rdr["Name"].ToString()
      '...
      Console.WriteLine()
    End While
  End Using
End Sub

Public Overrides Sub CreateNewOutputRows()
  'If you want to set any package variables, it must be done in this function
  'You will need to have already added these ReadWriteVariables in the Script Editor
  'For example:
  Variables.Success = True
End Sub

Name Parameters in the Command Text

You can avoid using NetSuiteParameter by passing in the parameters directly:

"EXEC GetRoles Account_Name=Checking, ..."

You can use the NetSuiteCommand object to execute stored procedure statements:

using System.Data.RSSBus.NetSuite;
...
public override void CreateNewOutputRows()
{
  string connectionString = "Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Offline=false;";
  using (NetSuiteConnection connection = new NetSuiteConnection(connectionString)) {
    NetSuiteCommand cmd = new NetSuiteCommand("EXEC GetRoles Account_Name = @Account_Name", connection);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add(new NetSuiteParameter("@Account_Name", "Checking"));
    // Add other parameters as needed ...

    NetSuiteDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read()) {
      Output0Buffer.AddRow();
      //Add output columns as necessary, for example:
      //Output0Buffer.Name = rdr["Name"].ToString();
      //...
      Console.WriteLine();
    }
  }
}

public override void PostExecute()
{
  //If you want to set any package variables, it must be done in this function
  //You will need to have already added these ReadWriteVariables in the Script Editor
  //For example:
  Variables.Success = true;
}

VB.NET

Imports System.Data.RSSBus.NetSuite
...
Public Overrides Sub CreateNewOutputRows()
  Dim connectionString As String = "Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;Offline=false;"
  Using connection As New NetSuiteConnection(connectionString)
    Dim cmd As New NetSuiteCommand("GetRoles Account_Name = @Account_Name", connection)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New NetSuiteParameter("@Account_Name", "Checking"))
    ' Add other parameters as needed ...

    Dim rdr As NetSuiteDataReader = cmd.ExecuteReader()
    While rdr.Read()
      Output0Buffer.AddRow()
      'Add output columns as necessary, for example:
      'Output0Buffer.Name = rdr["Name"].ToString()
      '...
      Console.WriteLine()
    End While
  End Using
End Sub

Public Overrides Sub CreateNewOutputRows()
  'If you want to set any package variables, it must be done in this function
  'You will need to have already added these ReadWriteVariables in the Script Editor
  'For example:
  Variables.Success = True
End Sub