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 SearchSuppliers stored procedure. You will need to add a reference to the TIBCO.SSIS2019.TDV.dll, which can be found in the lib subfolder of the installation folder.
C#
using System.Data.TDVClient;
...
public override void CreateNewOutputRows()
{
string connectionString = "Host=myHost;Domain=myDomain;DataSource=myDataSource;User=myUser;Password=myPassword";
using (TDVConnection connection = new TDVConnection(connectionString)) {
TDVCommand cmd = new TDVCommand("SearchSuppliers", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new TDVParameter("@Country", "US"));
// Add other parameters as needed ...
TDVDataReader 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.TDVClient
...
Public Overrides Sub CreateNewOutputRows()
Dim connectionString As String = "Host=myHost;Domain=myDomain;DataSource=myDataSource;User=myUser;Password=myPassword"
Using connection As New TDVConnection(connectionString)
Dim cmd As New TDVCommand("SearchSuppliers", connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New TDVParameter("@Country", "US"))
' Add other parameters as needed ...
Dim rdr As TDVDataReader = 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