This article describes TimeXtender Classic features related to SSIS and the configuration options.
SSIS Configuration Options
1. SSIS server name when configuring the TimeXtender Classic project repository
The SSIS server name specified on Project repository configuration tab 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 Integration Services / SSIS folder on the project level
Select if the project should use Integration Services. 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
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?
As stated in the TimeXtender Classic 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
Right-click on table -> Advanced -> Customized Code -> SSIS package (Add) -> Custom SSIS (Existing package)
SQL Server 2019 | SQLServerManager15.msc
SQL Server 2017 | SQLServerManager14.msc
Verify SSIS (SQL Server Integration Services) service is running
