To install and run SSIS Integration Toolkit for Microsoft Dynamics GP, your system must have the following components installed.
- A supported SSIS design-time or run-time, which can be one of the following:
- SSIS 2016
- SSIS 2014
- SSIS 2012
- SSIS 2008 R2
- SSIS 2008
- SSIS 2005
For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).
Note that for SSIS runtime, SQL Server Standard Edition or above is required to run our software. SQL Server Express or Web editions are not supported due to their own limitations.
Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.
- SQL Server 2014 Service Pack 2: https://www.microsoft.com/download/details.aspx?id=53168
- SQL Server 2014 Service Pack 1: https://www.microsoft.com/download/details.aspx?id=46694
For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).
- When targeting SSIS 2016, you would use SSDT for Visual Studio 2015 available for download at
- Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
- When targeting SSIS 2014, you can use either one of the following:
- SSDT for Visual Studio 2015: https://msdn.microsoft.com/mt186501.aspx (Packages created using SSDT 2015 need to have their project's TargetServerVersion setting set to "SQL Server 2014" in order to work with SSIS 2014)
- SSDT-BI for Visual Studio 2013: https://www.microsoft.com/download/details.aspx?id=42313
- When targeting SSIS 2012, you can use any one of the following:
- SSDT for Visual Studio 2015: https://msdn.microsoft.com/mt186501.aspx (Packages created using SSDT 2015 need to have their project's TargetServerVersion setting set to "SQL Server 2012" in order to work with SSIS 2012)
- SSDT-BI for Visual Studio 2012: https://www.microsoft.com/download/details.aspx?id=36843
- You can install the "SQL Server Data Tools" component that is shipped with the SQL Server 2012 installation media (which is based on a Visual Studio 2010 shell)
- When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version.
.NET Framework 3.5
- For any version before v1.3, .NET Framework 3.5 is required.
For v1.3 or later, .NET Framework 3.5 is only required when you use SSIS 2005.
- If you are using Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP operating systems, you would install .NET framework by downloading it from Microsoft website.
- If you are using Windows Server 2008 R2 or 2012 family of operating systems, .NET framework 3.5 should be installed using Server Manager program by adding .NET Framework 3.5.1 features.
- If you are using Windows 8, go to Control Panel -> Programs and Features -> Turn Windows features on or off, then select .NET Framework (includes .NET 2.0 and 3.0).
- For any version before v1.3, .NET Framework 3.5 is required. For v1.3 or later, .NET Framework 3.5 is only required when you use SSIS 2005.
- Windows Installer 4.5
- If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
- If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.
When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page, you will find two download links. One is for x86 and one is for x64 systems. Make sure to choose and download the right bit for your system.
After you have downloaded the package, you can install the software by following the instructions on your screen.
Setup a Database Connection using the Dynamics GP Connection Manager
SSIS Integration Toolkit for Dynamics GP includes a SSIS Connection Manager component to help you establish database connections to Microsoft Dynamics GP database catalogs.To add a new GP connection to your SSIS package, right-click the Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window, where you can select "DynamicsGP" and press "Add..." to create the connection.
The GP Connection Manager contains two tabs
- More Info
The Connection tab of the GP Connection Manager allows you to specify connection string settings.
- Server Name
The Server Name field allows you to specify the SQL Server address where your Dynamics GP database is located.
The authentication section allows you to specify your log in credentials to the SQL Server database, whether by using a windows account via integrated authentication or by using a SQL account by specifying a user name and password.
- Initial Catalog
The Initial Catalog field allows you to select the database catalog where your Dynamics GP is located.
- ErrorCodes Catalog
The ErrorCodes Catalog is used by the GP Destination Component to report the description of GP ErrorCodes. This should be the database catalog to GP's main catalog, usually 'DYNAMICS'. This is an optional field. If no value is provided, GP errors will be reported as integer values. You may query DYNAMICS.dbo.taErrorCodes for a list of ErrorCodes and their description.
- Dynamics GP Version
Specifying a different Dynamics GP version will load a different set of Dynamics GP schema to be used in GP source and destination components.
- Test Connection
After all the connection information has been provided, you may click "Test Connection" to test if the user can successfully login to the SQL Server database.
The More Info tabs shows some basic information about where to find support and additional information about the toolkit. In this page, you can find the version information of the toolkit.
Adding GP Components to the SSIS Toolbox
Before you can use the toolkit's data flow components, they must be added to your SSIS toolbox.
- If you are using SQL Server 2008 R2 or earlier, you must add them to SSIS toolbox by following the instructions below.
- If you are using SQL Server 2012 or later, you should not need to do so, as SQL Server 2012 (or a later version) automatically lists all available pipeline components by scanning the system. Under certain circumstances, your SSIS toolbox might appear empty, in which case, you should click the SSIS Toolbox button (shown below) on the top-right corner of your Control Flow or Data Flow view.
Adding GP Data Flow Components to the SSIS Toolbox (SQL Server 2008 R2 or earlier)
In BIDS 2008 or 2005 (depending on which SQL Server version you are using), create a new data flow if you do not have one yet, and switch to the data flow page. Then right-click on the toolbox area to bring up the context menu and select "Choose Items..." option as shown below.
You are now presented with a window called "Choose Toolbox Items". Switch to the "SSIS Data Flow Items" tab, and check the Dynamics GP components from the list.
You should now be able to use the component by dragging and dropping a component to the design surface of your SSIS data flow task.
Using the GP Source Component
The GP Source Component allows you to read data from your GP database. The component allows you to write custom queries to retrieve data, or simply choose a source object to read from.
The General page is where you will configure most of the set up details. You can specify your query, or select a source object to read data from.
- Connection Manager
The GP source component requires a database connection to your GP database catalog. You can specify a GP Connection Manager here to facility database connectivity.
- Source Type
Select either to retrieve data from a source object, or from a custom SQL query.
- Source Object
If the Source Type selected is Object this dropdown is enabled to let you select which object to read data from.
If the Source Type selected is SQLQuery, the SQL Query builder is shown. The SQL Query builder allows you to write custom SQL queries. You may also insert SSIS variables into your custom query.
The Columns page lists all the available metadata fields for the object that you wish to read from. You can select a subset of metadata fields that you wish to retrieve data for.
Using the GP Destination Component
GP Destination Component is an SSIS data flow pipeline component that can be used to write data to Microsoft Dynamics GP server. You may create, update, delete and void GP records using the GP Destination Component.
GP Destination Component includes the following three pages to configure how you want to write data to Microsoft Dynamics GP server.
- Error Handling
The General page is used to specify the general settings of the GP destination component. The Columns page allows you to map the columns from upstream components to GP fields in the destination table. The Error Handling page allows you to specify how errors are handled should they occur.
The General page of the GP Destination Component allows you to specify the general settings of the component.
- Connection Manager
The GP destination component requires a GP connection to connect with a Microsoft Dynamics GP server. The GP Connection Manager option will show all DynamicsGP connection managers that have been created in the current SSIS package or project.
The Action option allows you to specify how the data should be written to Microsoft Dynamics GP. There are three (3) action types available.
- Create - Create new record(s) in GP
- Update - Update existing record(s) in GP
- Delete - Delete record(s) from GP
- Destination Object
The Destination Object option allows you to specify which GP object to write data to. A drop down with available objects are listed here.
- Refresh Component button
By clicking this button, the component will retrieve the latest metadata defined in the GP Stored Procedure XML Schema File and update each field. After clicking this button, you will receive a status message indicating how many fields have been updated, added, or deleted.
- Map Unmapped Fields button
By clicking this button, the component will map any unmapped GP fields by matching their names with the input columns from the upstream component. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish associations between input columns and unmapped GP fields.
- Clear All Mappings button
By clicking this button, the component will remove all field mappings. You can use this button to reset your mappings.
The Columns page of GP Destination Component allows you to map the columns from upstream components to GP fields for the destination object.
In the Columns page, you will see a grid with five columns as seen above.
- Input Column - You can select a column from your upstream component to be used as the input of the corresponding GP field.
- Destination GP Field - The GP field that you are writing data.
- Data Type - This column indicates the type of value for the current GP field in GP system. Typically you would need to pass in the value using the format indicated in Data Type column.
- Required - Indicates whether the GP field requires an input.
- Default - The default value for the GP field if left unmapped.
Error Handling page
The Error Handling page allows you to specify how errors should be handled when they happen.
There are three options available.
- Fail on error
- Redirect rows to error output
- Ignore error
When the Redirect rows to error output option is selected, rows that failed to write to Dynamics GP will be redirected to the 'Dynamics GP Destination Error Output' output of the Destination Component. As indicated in the screenshot below, the green output connection represent rows that were successfully written, and the red 'Dynamics GP Destination Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Dynamics GP Destination Error Output' may contain the error message that was reported by Dynamics GP or the component itself.
NOTE: Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.
Using the GP Next Document Number Generator Component
The Dynamics GP NextDocNumber Editor / Generator is used to help you generate the next sequential ID number for given records that do not have automatic ID number generation. For example, you can use this component to generate an ID for a new Sales Order transaction.
- GP Connection Manager
Specify the Connection Manager to use so that we can call the appropriate stored procedures against the desired Dynamics GP instance.
- NextDocNumber Output Column
This is the name for the output column to create in the output buffer of the NextDocNumber SSIS component. The resulting output column can be used in the mapping of downstream components, and will contain the ID that you wish to generate.
- Document Transaction Type
Specify the Document Transaction Type
- Transaction Type
Specify the Transaction Type
If you selected the 'Sales Order Processing Transaction' Document Transaction Type, you will need to specify a value for the DOCID. Please see the table SOP40200 in your Dynamics GP database schema to find valid values. E.g. STDORD can be used here.
GP XML Schema Files
The Dynamics GP Components rely on XML Schema files to determine what objects and their field information are available for data manipulation. Upon installation, default schema files are installed into your installation folder. You may make changes to these schema files, or even indicate your own folder path to your version of the schema files via Registry key edit.
- Default Schema file location:
- %ProgramFiles%\KingswaySoft\SSIS Integration Toolkit for Microsoft Dynamics GP\Schemas
- Registry Key:
- HKey_Local_Machine\Software\KingswaySoft\SSIS Integration Toolkit for Microsoft Dynamics GP\SchemaDirectory
If you need any further assistance with the toolkit, please don't hesitate to contact us.