Microsoft SQL Server - Load data - Native Load Control - 1.6

Before loading the data from your Source(s) System(s) to your Microsoft SQL Server Target System, please:

• Deploy the generated Artifacts

You are now ready to load the data.

There are two possibilities to load the data natively with Microsoft SQL Server:

• Sequentially on a single thread
• In parallel with a multi-thread approach

You can also load the data with an external tool as:

• Apache Airflow

Load the data sequentially

To load the data sequentially:

• Open Powershell
• Navigate to your generated artifacts folder:

--Example
cd C:\XXXXXXXXX\20240219144515

• Execute the Powershell script helper for load:

.\execute.ps1

• The prompt asks you for the server or instance name:
  
• Enter a server or instance name
  

• Enter a username
  

• If you enter a username, enter the password
  
• You have a similar result: 
  
  -------------------------------------------------------------------------------------------------------------------------------
  
• If you want to check the logs of the execution:
  • Open SQL Server Management Studio
  • Connect to the target database
  • Execute the following statement:

--Adapt the execution plan name
EXEC [LC].[Show_Execution] @ExecutionPlan = N'DEV_XXXX'; 

• The logs should look like the following. An essential piece of information is the number of rows inserted:
  
• You can now check that your data were correctly loaded with the following script:

SELECT
SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount],
Tables.type_desc
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] =
[Partitions].[object_id] AND [Partitions].index_id IN (0, 1)
WHERE SCHEMA_NAME(schema_id) NOT IN ('LC') --We exclude the Load Control tables
GROUP BY SCHEMA_NAME(schema_id),[Tables].name, Tables.type_desc
ORDER BY SCHEMA_NAME(schema_id), TotalRowCount DESC;

Load the data with a multi-thread approach

To load the data with a multi-thread approach:

• Don't use the Powershell script helper for load execute.ps1
• Open SQL Server Management Studio (or any SQL databases management tool)
• Open the LoadControl_Execution_XXXX.sql (from LoadControl folder)
• Copy the code in other query tabs as many as you want different threads
• Execute the code from all the tabs at the same time

• If you want to check the logs of the execution:
  • Execute the following statement:

--Adapt the execution plan name
EXEC [LC].[Show_Execution] @ExecutionPlan = N'DEV_XXXX'; 

• The logs should look like the following. An essential piece of information is the number of rows inserted:
  
• You can observe in this example that 3 threads were used for the load:

• You can now check that your data were correctly loaded with the following script:

SELECT
SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount],
Tables.type_desc
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] =
[Partitions].[object_id] AND [Partitions].index_id IN (0, 1)
WHERE SCHEMA_NAME(schema_id) NOT IN ('LC') --We exclude the Load Control tables
GROUP BY SCHEMA_NAME(schema_id),[Tables].name, Tables.type_desc
ORDER BY SCHEMA_NAME(schema_id), TotalRowCount DESC;