Note: Applies To: Version 20 and earlier. The use of SSIS is not recommended in projects with ODX Server.  The original article was written for Discovery Hub version: 17.12.8. and SQL Server 2016, with some updates for later versions.

This article talks about TimeXtender features related to SSIS and the configuration options.

SSIS Integration Options

Before considering the options, we need to clarify the related features in TimeXtender.

1. SSIS server name on Project repository

In older versions, the dialog looks like the following:

The SSIS server name specified on Project repository configuration is only for synchronizing data sources that use SSIS for transfer. Once you synchronize a data source, InformationSchema SSIS packages will be created accordingly on the SSIS server.

2. Use SSIS/SSIS folder on the project level

On the project level, there is only the option to select if using Integration Services or not. The SSIS server names can only be specified on the individual database if it is different from the default database server. You can also specify the custom SSIS folder. The custom SSIS root folder will be created under the MSDB of the SSIS server.

3. Use SSIS for transfer As Parent v.s. Yes

Use SSIS for transfer AS Parent means using the SSIS transfer setting from the parent level of the database. For each database type, the parent is as follows.

You can only specify the SSIS server name on the transfer target database; meaning Data Warehouse for transfer from Staging Database to Data Warehouse and Staging Database for transfer from Data Source to Staging Database.

4. Use Remote SSIS package execution

Remote Execution of SSIS Packages allows you to use the SSIS service on a remote machine. The feature is intended for the case when the database server is separated from application server where TimeXtender is installed and running. We can use SSIS service on database server instead of application server if remote SSIS package is installed on the database server.

SSIS Behind the Scenes

When TimeXtender is installed on the database server, all SSIS components will also be installed and running on the same database server. We will only discuss the scenario when TimeXtender is installed on an application server that does not house the Microsoft SQL Server Installation.

1. Where should I install SSIS?

According to the TimeXtender Prerequisites, SSIS is required on both database server and application server when SSIS is utilized. Thus, you must install SSIS on both database server and application server.

SSIS on the application server may look unnecessary when you use remote SSIS to run the service and save the SSIS packages to the MSDB on the database server. However, TimeXtender needs certain SSIS components on the application server to use a managed API and create and run the SSIS packages. When creating SSIS packages, TimeXtender needs access to the proper version of the API – the SSIS DLL. That is why the different version of SSIS needs to be installed covering every version of DB that TX uses for Source System (including the Repository).

From a license perspective, additional SSIS license is needed on the application server. Microsoft does not allow others to distribute the DLLs and it’s much too complicated for TimeXtender to sort out the licensing scenarios for Microsoft. Thus, any intention to limit the SQL Server licensing cost and only pay for the database server by splitting the servers is not a reasonable cause.

Error – Could not load file or assembly

SSIS is always required on the server where TimeXtender is installed. You will encounter the following error when SSIS is not available in the application server.

2. Where is my SSIS service running?

When TimeXtender is installed on the application server that is different from the database server, SSIS service will have 2 options to be configured.

• By default, it’s going to use SSIS service on the application server where TimeXtender is installed
• By installing Remote SSIS package on the database server, it’s going to use SSIS service on the database server

With the default setting, all traffic must pass through the machine where TimeXtender opens and executes the SSIS packages it has generated. Thus, it is recommended to use Remote SSIS package to reduce the potential network delay when TimeXtender and databases are in separate servers. However, it is important to remember that Remote SSIS package only impacts where your SSIS service is running but is not any form of replacement for your SSIS server or packages.

3. Where are my SSIS packages?

By default, your SSIS server is your database server. You can specify a different SSIS server by input in SSIS server name in either Staging database or Data warehouse.

The SSIS server name will be used to resolve the SSIS server address. By deploying SSIS transfer step, TimeXtender saves the package to the resolved server. Be aware the package is saved in MSDB through SQL Server but not Integration Services. Thus, this will only work if the Integration Services is set up to use SQL Server instance on the same server.

To browse your SSIS packages in SSMS, you connect to your SSIS server (default database server) and look for the folder under MSDB. You can modify your MsDtsSrvr.ini file in an application server to change the server of MSDB and browse the SSIS packages remotely. However, the packages are eventually only saved under the configured SSIS server with the SQL Server instance.  

Error – The SQL Server instance specified in SSIS service configuration is not present or is not available

You will encounter this error when SSIS server is specified on an application server without SQL Server.

You either leave the SSIS server name as blank to use database server or specify another SSIS server that has SQL Server installed on the server in some edge cases. In most cases, you will treat your database server as your SSIS server.

Prerequisite and Recommendation

In summary, these are the prerequisites and recommendations to consider SSIS configuration when TimeXtender is installed on an application server that does not house the Microsoft SQL Server.

1. SSIS must be installed on both database server and application server (or a client local machine).

2. Leave SSIS server name as blank to use the database server as your SSIS server by default

3. If you choose to use a separated SSIS server from the database server, you also need SQL Server instance installed on the dedicated SSIS server.

4. Consider using Remote SSIS execution to run SSIS service on the database server to improve transfer performance.

5. Use ADO.NET or Direct Read instead if SSIS configuration becomes too complicated

How to locate the SSIS packages TimeXtender creates