posts - 88 , comments - 3 , trackbacks - 0

Call Stored Procedures from Script Components

The source task and the destination task are the easiest ways to transfer data in SSIS. You can use source and script components to output data to the data flow.

This article shows how to create a script component that functions as a source in the data flow: It passes data returned from Salesforce to downstream components. You will execute the CreateJob and CreateBatch stored procedures available in the Salesforce SSIS Component.

Stored procedures can be used to search, update, and modify data. Stored procedures surface functionality of the underlying API that cannot be modeled as SELECT, INSERT, UPDATE, and DELETE operations.

See Use the CData SSIS Components to Download Files from a SharePoint Site for an example of using stored procedures in a transformation component.

See the "Getting Started" chapter in the help documentation for an example of calling stored procedures using source components at design-time.

  1. Open Visual Studio and create a new Integration Services Project.
  2. Add a new Data Flow Task to the Control Flow window.
  3. Open the Data Flow Task and add a Script Component to the data flow. Select the source type, as the columns returned by the stored procedure will be output to other components.
  4. Double-click the Script Component to open the editor.
  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.

    You can find the available columns in the "Data Model" chapter in the help documentation.

  6. Click Script -> Edit Script to open Visual Studio and generate the method definitions.
  7. In the CreateNewOutputRows() function, you can add code to execute the stored procedure. The example below calls the CreateJob procedure to create a job that inserts Salesforce contacts. The CreateBatch stored procedure is then called to insert a batch of two contacts.
  8. //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
    //The JobId is saved as an input to the CreateBatch stored procedure.
    SalesforceDataReader rdr = cmd.ExecuteReader();
    String JobId = "";
    while (rdr.Read()) {
    JobId = (String)rdr\["JobId"];
    //Create the command for CreateBatch. The example adds 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>"
    //Execute CreateBatch
    SalesforceDataReader batRdr = batCmd.ExecuteReader();
  9. If you specified output columns earlier, you can now add data into them using the UserComponent Output0Buffer. For example, you can set a value for the JobId by modifying the DataReader that contains the output of CreateJob:
  10. while (rdr.Read()) {
    JobId = (String)rdr\["JobId"];
    Output0Buffer.JobId = JobId;

The output columns can be used in the data flow. For example, you can write the values to a Flat File Destination. Run the project to pass the output columns to the downstream component.

Print | posted on Tuesday, November 27, 2012 8:36 AM | Filed Under [ salesforce cdata ssis ]


No comments posted yet.
Post A Comment

Powered by: