Using Stored Procedures in SSIS
- by dataintegration
The SSIS Data Flow components: the source task and the destination task are the easiest way to transfer data in SSIS. Some data transactions do not fit this model, they are procedural tasks modeled as stored procedures. In this article we show how you can call stored procedures available in RSSBus ADO.NET Providers from SSIS.
In this article we will use the CreateJob and the CreateBatch stored procedures available in RSSBus ADO.NET Provider for Salesforce, but the same steps can be used to call a stored procedure in any of our data providers.
Step 1: Open Visual Studio and create a new Integration Services Project.
Step 2: Add a new Data Flow Task to the Control Flow window.
Step 3: Open the Data Flow Task and add a Script Component to the data flow pane. A dialog box will pop-up allowing you to select the Script Component Type: pick the source type as we will be outputting columns from our stored procedure.
Step 4: Double click the Script Component to open the editor.
Step 5: In the "Inputs and Outputs" settings, enter all the columns you want to output to the data flow. Ensure the correct data type has been set for each output. You can check the data type by selecting the output and then changing the "DataType" property from the property editor. In our example, we'll add the column JobID of type String.
Step 6: Select the "Script" option in the left-hand pane and click the "Edit Script" button. This will open a new Visual Studio window with some boiler plate code in it.
Step 7: In the CreateOutputRows() function you can add code that executes the stored procedures included with the Salesforce Component. In this example we will be using the CreateJob and CreateBatch stored procedures. You can find a list of the available stored procedures along with their inputs and outputs in the product help.
//Configure the connection string to your credentials
String connectionString = "Offline=False;user=myusername;password=mypassword;access token=mytoken;";
using (SalesforceConnection conn = new SalesforceConnection(connectionString)) {
//Create the command to call the stored procedure CreateJob
SalesforceCommand cmd = new SalesforceCommand("CreateJob", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SalesforceParameter("ObjectName", "Contact"));
cmd.Parameters.Add(new SalesforceParameter("Action", "insert"));
//Execute CreateJob
//CreateBatch requires JobID as input so we store this value for later
SalesforceDataReader rdr = cmd.ExecuteReader();
String JobID = "";
while (rdr.Read()) {
JobID = (String)rdr["JobID"];
}
//Create the command for CreateBatch, for this example we are adding two new rows
SalesforceCommand batCmd = new SalesforceCommand("CreateBatch", conn);
batCmd.CommandType = CommandType.StoredProcedure;
batCmd.Parameters.Add(new SalesforceParameter("JobID", JobID));
batCmd.Parameters.Add(new SalesforceParameter("Aggregate", "<Contact><Row><FirstName>Bill</FirstName>"
+ "<LastName>White</LastName></Row><Row><FirstName>Bob</FirstName><LastName>Black</LastName></Row></Contact>"));
//Execute CreateBatch
SalesforceDataReader batRdr = batCmd.ExecuteReader();
}
Step 7b: If you had specified output columns earlier, you can now add data into them using the UserComponent Output0Buffer. For example, we had set an output column called JobID of type String so now we can set a value for it. We will modify the DataReader that contains the output of CreateJob like so:.
while (rdr.Read())
{
Output0Buffer.AddRow();
JobID = (String)rdr["JobID"];
Output0Buffer.JobID = JobID;
}
Step 8: Note: You will need to modify the connection string to include your credentials. Also ensure that the System.Data.RSSBus.Salesforce assembly is referenced and include the following using statements to the top of the class:
using System.Data;
using System.Data.RSSBus.Salesforce;
Step 9: Once you are done editing your script, save it, and close the window. Click OK in the Script Transformation window to go back to the main pane.
Step 10: If had any outputs from the Script Component you can use them in your data flow. For example we will use a Flat File Destination. Configure the Flat File Destination to output the results to a file, and you should see the JobId in the file.
Step 11: Your project should be ready to run.
