Ready to get started?

Learn more about Sage US Connectivity Solutions

Learn More

Export Data from SQL Server to Sage US through SSIS

Easily push SQL Server data to Sage US using the CData SSIS Tasks for Sage US.

SQL Server databases are commonly used to store enterprise records. It is often necessary to move this data to other locations. The CData SSIS Task for Sage US allows you to easily transfer Sage US data. In this article you will export data from SQL Server to Sage US.

Add Source and Destination Components

To get started, add a new ADO.NET Source control and a new Sage US Destination control to the data flow task.

Configure the ADO.NET Source

Follow the steps below to specify properties required to connect to the SQL Server instance.

  1. Open the ADO.NET Source and add a new connection. Enter your server and database information here.
  2. In the Data access mode menu, select "Table or view" and select the table or view to export into Sage US.
  3. Close the ADO NET Source wizard and connect it to the destination component.

Create a New Connection Manager for Sage US

Follow the steps below to set required connection properties in the Connection Manager.

  1. Create a new connection manager: In the Connection Manager window, right-click and then click New Connection. The Add SSIS Connection Manager dialog is displayed.
  2. Select CData Sage50US Connection Manager in the menu.
  3. Configure the connection properties.

    The Application Id and Company Name connection string options are required to connect to Sage as a data source. You can obtain an Application Id by contacting Sage directly to request access to the Sage 50 SDK.

    Sage must be installed on the machine. The Sage.Peachtree.API.dll and Sage.Peachtree.API.Resolver.dll assemblies are required. These assemblies are installed with Sage in C:\Program Files\Sage\Peachtree\API\. Additionally, the Sage SDK requires .NET Framework 4.0 and is only compatible with 32-bit applications. To use the Sage SDK in Visual Studio, set the Platform Target property to "x86" in Project -> Properties -> Build.

    You must authorize the application to access company data: To authorize your application to access Sage, restart the Sage application, open the company you want to access, and connect with your application. You will then be prompted to set access permissions for the application in the resulting dialog.

    While the compiled executable will require authorization only once, during development you may need to follow this process to reauthorize a new build. To avoid restarting the Sage application when developing with Visual Studio, click Build -> Configuration Manager and uncheck "Build" for your project.

Configure the Sage US Destination

In the destination component Connection Manager, define mappings from the SQL Server source table into the Sage US destination table and the action you want to perform on the Sage US data. In this article, you will insert Customer entities to Sage US.

  1. Double-click the Sage US destination to open the destination component editor.
  2. In the Connection Managers tab, select the connection manager previously created.
  3. In the Use a Table, menu, select Customer. In the Action menu, select Insert.
  4. On the Column Mappings tab, configure the mappings from the input columns to the destination columns.

Run the Project

You can now run the project. After the SSIS Task has finished executing, data from your SQL table will be exported to the chosen table.