Executing SQL Statements and Stored Procedures

 

The Execute SQL activity allows you to execute one or many SQL statements and stored procedures in one step. The activity supports inserting, selecting, updating, and deleting records from a database. If you are familiar with writing SQL statements in SQL Server Management Studio, then you can use the same statements in the Execute SQL activity.

 

1. Check out and open the process.
    
2. In the Toolbox pane, click Database. A list is displayed.
    
3. Double-click or drag the Execute SQL activity into the process.
    
4. Link the activity with other activities.
    
5. Right-click the Execute SQL activity. A shortcut menu is displayed.
    
6. Select View Properties. The Execute SQL window is displayed.
    
7. Click the Database Resource and Query tab. The corresponding screen is displayed.
     
8. Click the System Resource editor in the Database Resource field. A drop-down list is displayed.
     
9. Click the ODBC system resource.
   
   

   Note:

   If your ODBC System Resource uses a data item, then click the Configuration values button to enter a connection sting for design-time configuration.

   
    
10. In the Enter SQL to Execute field enter the query, queries or stored procedures you want to execute
    
    

    Note:

    If you included one or more data items in your query, click the Configuration values button to enter test values for those data items. If you do not enter test values, then your query may not execute successfully and you will not be able to complete the data mappings.

    
      
11. Click the Data Mappings tab.
        
12. Click the Generate Mapping Grids button. A result set for each SQL statement that returns a value is displayed.
      
    

    Note:

    Click the Select Collection Data Item Member for Mapping check box if you are populating a collection data item and expect more than one result for a specific statement.

     
      
13. In the Data Item field, click the Data Item editor icon and select a data item to hold the value of a column.
      
    

    Note:

    Calculation data items are read-only. You cannot edit this type of data item.

    
      
    

    Note:

    You can add a mapping by clicking the plus sign (+). Use the minus sign (-) to remove a mapping.

        
       
14. In the Data Column field, click the drop-down arrow and select the column with the value you want the data item to hold.       
      
    

    Note:

    If you want to populate multiple data items with column values, repeat steps 13 and 14 for each. Add rows for mapping data items to columns by clicking the plus sign (+). Use the minus sign (-) to remove a mapping.

    
      
    

    Note:

    If you want to populate multiple data items with column values, repeat steps 13 and 14 for each. Add rows for mapping data items to columns by clicking the plus sign (+). Use the minus sign (-) to remove a mapping.

     
     
15. Click OK. You configured the activity to execute SQL statements and stored procedures.