SSIS Components for SendGrid

Build 20.0.7587

Calling Stored Procedures

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

Call Stored Procedures from a Source Component

Follow the steps below to execute a stored procedure with the SQL EXEC keyword:

  1. Double-click the component in the Data Flow task to open the editor.
  2. In the Data Access Mode menu, select SQL Command. The query syntax 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';

    EXECUTE and EXEC can be used interchangeably. See Using the Source Component for how to parameterize the query.

Call Stored Procedures from a Script Component

The following sections show how to call stored procedures in a script component.

Add the Script Component

Add a script component to the data flow from the toolbox and select the type of script component:

  • 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, double-click the component to open the Script Transformation Editor and follow the steps below:

  1. 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.
  2. Add any SSIS package variables in the ReadOnlyVariables or ReadWriteVariables lists on the Script tab. Note that read/write variables can only be set in the PostExecute method in the script (see the example script below).
  3. 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. The Example Script section 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 window and resolve them as needed. The script component is now ready to use.

Example Script

The example below shows how to use a script component to call the AddRecipientToList stored procedure. You will need to add a reference to the CData.SSIS2020.SendGrid.dll, which can be found in the lib subfolder of the installation folder.

C#

using System.Data.CData.SendGrid;
...
public override void CreateNewOutputRows()
{
  string connectionString = "APIKey=abc123;";
  using (SendGridConnection connection = new SendGridConnection(connectionString)) {
    SendGridCommand cmd = new SendGridCommand("AddRecipientToList", connection);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add(new SendGridParameter("@ListId", "63777"));
    // Add other parameters as needed ...

    SendGridDataReader 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.CData.SendGrid
...
Public Overrides Sub CreateNewOutputRows()
  Dim connectionString As String = "APIKey=abc123;"
  Using connection As New SendGridConnection(connectionString)
    Dim cmd As New SendGridCommand("AddRecipientToList", connection)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SendGridParameter("@ListId", "63777"))
    ' Add other parameters as needed ...

    Dim rdr As SendGridDataReader = 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

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587