Frequently Asked Questions

SSIS Integration Toolkit for Microsoft Dynamics SL

Frequently asked questions (FAQ) on Development, Licensing, and Deployment

Development

How do I upgrade the software? What precautions should I take?

We always recommend keeping our software up-to-date. There are new features and enhancements along with bug fixes that are implemented into our products every release. In order to upgrade a licensed system to the latest software release, you need to make sure that you have up-to-date software maintenance for that particular license, which you can find out by checking the Maintenance Expiry Date after launching the KingswaySoft License Manager program. You need to make sure that your maintenance date is greater than the release date of a particular version that you want to upgrade to. In case that your software maintenance has expired, you can reach out to us for a renewal quote, we will be happy to assist you. Note that software maintenance is only applicable to perpetual licenses, not subscription licenses, as software maintenance is always included in subscription license, in which case you can upgrade to any version as long as your subscription has not expired. For the free developer license, you have the same freedom to upgrade, but we typically recommend you stay with that same version that you use for your production environment to maintain the maximum compatibility when you deploy SSIS packages.

Once you have confirmed you have an up-to-date maintenance that qualifies you for the upgrade, then you can proceed with the upgrade process. Best practices would be to uninstall the old version before installing the new version (our software does support in-place upgrade without having to uninstall first, but there are cases the in-place upgrade doesn't clean up the old version properly). We usually try to maintain maximum backward compatibility so your packages will continue to work after the upgrade. However, you will want to pay attention to any Breaking Changes that may have been introduced between versions, in which case you may need to make changes to your SSIS packages to work with the new version that you are upgrading to. It is highly possible that those Breaking Changes (if any at all) may not even affect you, in which case you do not need to do anything with your packages to have them work with the newer version.

We generally recommend you try out our new release in a DEV or test environment before implementing the new version in your production environment.

Information on any Breaking Changes can be found on our Change Log page. Some of those changes can be addressed by simply refreshing the component, while the others may require some additional efforts.

Why was the Solomon Object Model not used to create the toolkit components?

Upon investigation and experimentation with the Solomon Object Model, we found that the technologies used in the provided libraries were too primitive. Due to the way the Solomon Object Model mimics the Dynamics SL client, we couldn't find a robust solution to efficiently extract data nor input data.

Why are all text fields displayed as nvarchar with a length of 4000?

Text fields are generally represented as 'nvarchar'. This is a general practice.

When it comes to the question why the length is always 4000, it is related to web service interface that we use to make service calls to the Dynamics SL web service server. In order to be able to work with the web service interfaces, we rely on its WSDL document to generate SSIS metadata. The WSDL documents that are exposed by the web services do not have a length specified for text fields. For this reason, we choose to render all text fields to its maximum allowed length, which is 4000.

You might run into a situation when a field's output contains more than 4000 characters, in which case it will overflow the buffer. To rectify the problem, you can change the field's type to DT_NTEXT using SSIS Advanced Editor window.

How can I expose more objects / web services for consumption by the toolkit components?

In your Dynamics SL installation package, install the User Manuals and read the Web Services SDK document (WebServices.pdf). Contained within the guide is documentation detailing the default web services. The section which may interest you the most is the chapter on Authoring a Custom Web Service.

How do I maximize the throughput when writing data to Microsoft Dynamics SL?

Depending on your deployment, the strategy of optimization can be a little different.

What else do I need to do if I want to use the BDD component? Is there anything else I should be aware of?

To make full use of the BDD component, you need to increase the connection limit that is imposed by the Microsoft .NET framework, which is a maximum of 2 connections per host (e.g. server) for service calls.

In order to overwrite this limit, you must modify DTExec.exe.config and DtsDebugHost.exe.config files under DTS\binn folder by adding the following connectionManagement section.

<configuration>
  ...
  <system.net>
    <connectionManagement>
      <add address="*" maxconnection="100"/>
    </connectionManagement>
  </system.net>
</configuration>

The above configuration allows up to 100 connections per host at the same time. You may change the number based on your needs.

If you are running a 64-bit system, you must make changes to the files under "Program Files" and "Program Files (x86)" folders. 

When you use the BDD component, there are a couple of things that you should be aware of. 

  • You may not want to use too many BDD distributions in your data flow tasks, as it could overload your server. 
  • BDD uses SSIS internal buffer to manage the distribution, which is a preset size defined at the data flow task level. The default buffer size is 10,000 rows, which means BDD only starts to distribute incoming rows after one branch has used up the buffer. For instance, the BDD component's second output will not receive anything if you have less than 10,000 records (9,947 records actually), and the third output will not receive anything until you have more than 19,894 records. If you are working on a small load, you must change the data flow task's DefaultBufferMaxRows property and give it a smaller number so that the BDD distribution happens sooner. 
  • SSIS has an undocumented limitation on the number of active buffers for each data flow task, which is 5. Because of this limitation, you should not have more than 5 BDD branches in one data flow task, as doing so will not provide any performance benefits on top of having 5 BDD branches. If you want to use more than 5 BDD components, you would need to split your source data into multiple data flow tasks, and have up to 5 BDD components in each data flow task. For instance, you can have 3 data flow tasks that write to 5 destination components simultaneously, which gives you a total of 15 concurrent threads using 15 BDD branches in total.

Licensing

Do I need a trial license to evaluate the software? 

You do not need to acquire a license if you just want to test out the software functionality within the development tools (SSDT-BI, BIDS - Business Intelligence Development Studio, or Visual Studio). After installation, the software will operate under the free developer license by default. With the developer license, you can create and develop data flows within the development tools, including executing test loads without requiring a commercial license. 

If you want to evaluate whether the software functions properly outside of the development tools (SSDT-BI, BIDS, or Visual Studio), you can acquire a trial license using the License Manager program which is installed along with the software. After you have acquired a trial license, you will be able to run SSIS packages on a scheduled basis, or from any other Windows process such as command line. The trial software will operate fully-functional for the trial period, which is generally 14 days. When the trial period expires, the software will revert back to the free developer license. 

What are the limitations of the free developer license? 

The free developer license is fully-functional within your development tools (SSDT-BI, BIDS, or Visual Studio). The main limitation is the inability to run the software outside your development tools. In addition, you should not use the free development license for production purposes (mainly production data extraction or data load).

What's the difference between subscription license and perpetual license? 

There is no functionality difference between subscription license and perpetual license. However, with a subscription license, your capability to run the software outside the development tools (such as running SSIS packages on a scheduled basis) is limited to your subscription period. With the perpetual license, you can run the designated version of software within or outside the development tools (SSDT-BI, BIDS, or Visual Studio) for as long as you want.

Deployment

When I attempt to deploy an SSIS package or when executing an SSIS Package on SQL Server Management Studio for SQL Server 2014, I receive the following error message 'Unexpected Termination' (or System.AccessViolationException).

When using SQL Server 2014, a cumulative update is required (cumulative update 2 or later, or any recent service packs). The following is the list for the download links.