Microsoft SQL Server Source

MSSQL data sources connect to SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or Azure SQL Servers for its source data.

There are three ways of connecting to a source SQL Server in Profitbase Studio; via linked server, PowerShell or via SSIS.

Linked Server or SSIS using SQL Server Destination Adapter is most often recommended if source data is on same SQL Server. Performance and memory utilization is better for SSIS using SQL Server Destination compared to SSIS using OLEDB Destintion.

Linked Server is usually the slowest option.

Note! For MSSQL; PowerShell differs from the other connection methods in that it performs the transfer in two stages. First from source to staging and then from staging to data warehouse table adding column mappings in the process. This enhances the need to select only relevant columns for import to the staging table.

 

Example - loading data from external SQL Server

1. Using MSSQL SSIS; set Destination Adapter to SQL Server Destination: (Only suggested if SQL Server is same as solution/data warehouse SQL server)

 

 

Performance test: Second time reload of source table using SSIS with SQL Server Destination took 4 minutes 36 seconds.

 

2. Using MSSQL SSIS; set Destination Adapter to OLEDB Destination:

 

 

Overwrite existing SSIS Destination Adapter settings for all data source tables:

 

 

Performance test: Second time reload of source table using SSIS with OLEDB Destination took 4 minutes 21 seconds.

3. Change Connection Method to Linked Server:

 

Performance test: Second time reload of source table using linked server took 8 minutes 56 seconds.

 

4. Change Connection Method to Power Shell:

 

Overwrite existing table prefixes:

 

Performance test: Second time reload of source table using PowerShell took 7 minutes 53 seconds.