SSIS Components for Bugzilla

Build 21.0.7930

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 SampleProcedure stored procedure. You will need to add a reference to the CData.SSIS2021.Bugzilla.dll, which can be found in the lib subfolder of the installation folder.


using System.Data.CData.Bugzilla;
public override void CreateNewOutputRows()
  string connectionString = " Url=http://<yourdomainname>/Bugzilla;APIKey=abc123;";
  using (BugzillaConnection connection = new BugzillaConnection(connectionString)) {
    BugzillaCommand cmd = new BugzillaCommand("SampleProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add(new BugzillaParameter("@Id", "7"));
    // Add other parameters as needed ...

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

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;


Imports System.Data.CData.Bugzilla
Public Overrides Sub CreateNewOutputRows()
  Dim connectionString As String = " Url=http://<yourdomainname>/Bugzilla;APIKey=abc123;"
  Using connection As New BugzillaConnection(connectionString)
    Dim cmd As New BugzillaCommand("SampleProcedure", connection)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New BugzillaParameter("@Id", "7"))
    ' Add other parameters as needed ...

    Dim rdr As BugzillaDataReader = cmd.ExecuteReader()
    While rdr.Read()
      'Add output columns as necessary, for example:
      'Output0Buffer.Name = rdr["Name"].ToString()
    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) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930