Copy On Prem Regular Table to Partition Table in SQL SERVER in Azure ADF

Question

I have requirement to copy a Table (Structured data) to Same structure Partitioned Table on Date as partitioned column.

I have check that there is option in 'Source' in copy data activity to use Partition and dynamic partition option. But can the Sink be Partitioned Table?

Eg->

Table ->

Employee -> Name, Age, DOB, Created_date

Partitioned Table ->
Employee_BK -> Name, Age , DOB, Created_Date (Partitioned column).

I have tried copy data activity and Source is having 'Partition' Button but no such option in 'Sink'

Answer 1

As @ChenHirsh mentioned, you can create a Partition table in SQL Server according to your partition requirements using the code below:

CREATE PARTITION FUNCTION PF_Employee_BK (DATETIME)
AS RANGE RIGHT FOR VALUES ('<range>');

CREATE PARTITION SCHEME PS_Employee_BK
AS PARTITION PF_Employee_BK
TO ([PRIMARY],<failoverGroups...>);
CREATE TABLE Employee_BK
(
    <columns...>
)
ON PS_Employee_BK (Created_Date);

Create a source dataset with a regular table and select the partition table in the sink dataset for copy activity in ADF. Then, all rows will be sorted into the appropriate partitions.

You can check how many rows are sorted according to your partition in the table using the query below:

SELECT 
    object_name(object_id) AS TableName,
    index_id,
    partition_number,
    rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('<partitionedTableName>');