Using the Profile in SSIS

The CData SSIS Components for API provide data flow components that allow for straightforward integration with API Profiles from SSIS data flow tasks. SQL Server 2014, 2016, 2017, and 2019 are supported including their corresponding version of SQL Server Data Tools.

Connecting with the API Connection Manager

Adding a new API Profile connection to the SSIS package is straightforward. Right-click within the Connection Manager window and select New Connection from the menu. Then, choose the API Connection Manager from the Add SSIS Connection Manager window.

In order to connect to the profile, set the connection property Profile to the path of the profile, and ProfileSettings to the required profile properties defined in the Profile Connection page.

Querying API Profile Data with the Source Component

Follow the procedure below to connect to API Profiles, retrieve data, and provide data to other components in the workflow.

1. In the SSIS Toolbox, drag the CData API source component into the Data Flow task.
2. Double-click the CData API source component. The CData API Source Editor will display.
3. In the Connection Managers menu, select an available CData API connection manager, or create a new instance if one is not already available.
4. Choose your Access Mode: "Table or View" or "SQL Statement". Select "Table or View" to use the GUI to select a table or view. Select "SQL Statement" to configure a statement of your choice.
5. Select the Columns tab and rename any output columns as desired.
6. When you execute the data flow, rows from your selected table or statement will be made available to the components in the data flow.
7. See SELECT Statements for the supported SQL syntax and examples. See Calling Stored Procedures for how to call stored procedures.

When you execute the data flow, rows from your selected table or statement will be made available to the components in the data flow.