Azure Data Factory Copy Activity to copy all Tables, Views and Stored Procedures in single Copy Activity from SQL Server - Stack

admin管理员组

文章数量:1122832

Can someone let me know if its possible to copy all the tables, stored procedures and views from one SQL DB to an Azure SQLDB in a single copy activity?

For the source dataset I have the following copy activity:

And for the sink I have the following:

The above I believe will copy and create all the tables, but I'm not sure I copy and create the stored procedures, views, etc...

From the answer provided by @Bhavani, can someone let me know how to

Add source and sink datasets with two string parameters Schema and Table define them as @dataset().Schema for schema, @dataset().Table

I'm added the Schema and TableName,as described, see image, but I'm getting the error "Table is required for Copy activity"

I'm getting there. I fixed the error "Table is required for Copy activity". Now I'm getting the error "The expression 'length(activity('Lookup1').output.value)' cannot be evaluated because property 'value' doesn't exist, available properties are 'firstRow, effectiveIntegrationRuntime, billingReference, durationInQueue'.

Can someone let me know if its possible to copy all the tables, stored procedures and views from one SQL DB to an Azure SQLDB in a single copy activity?

For the source dataset I have the following copy activity:

And for the sink I have the following:

The above I believe will copy and create all the tables, but I'm not sure I copy and create the stored procedures, views, etc...

From the answer provided by @Bhavani, can someone let me know how to

Add source and sink datasets with two string parameters Schema and Table define them as @dataset().Schema for schema, @dataset().Table

I'm added the Schema and TableName,as described, see image, but I'm getting the error "Table is required for Copy activity"

I'm getting there. I fixed the error "Table is required for Copy activity". Now I'm getting the error "The expression 'length(activity('Lookup1').output.value)' cannot be evaluated because property 'value' doesn't exist, available properties are 'firstRow, effectiveIntegrationRuntime, billingReference, durationInQueue'.

• azure-data-factory

Share Improve this question Follow edited Nov 22, 2024 at 19:46 Patterson asked Nov 21, 2024 at 20:47 PattersonPatterson 2,75177 gold badges4949 silver badges138138 bronze badges 4

• can you add what you have tried? – Pratik Lad Commented Nov 22, 2024 at 3:14
• @PratikLad, I have updated the question. Thanks – Patterson Commented Nov 22, 2024 at 7:50
• tbh, @PratikLad, I would also like to know if my request is even possible? – Patterson Commented Nov 22, 2024 at 10:05
• You can copy all views and tables to the target database using ADF. But stored procedures are not supported to copy. – Bhavani Commented Nov 22, 2024 at 10:09

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

According to the MS document SQL server dataset only supported to select table or View, not for Stored procedure. According to this

ADF is not the right tool to copy stored procedures from one dn to another db. Use the Visual Studio Schema Compare and Data Compare features instead.

You can copy all tables/views from one Azure SQL database to target database tables using one copy activity as follows:

Create dataset of source database and add it look up activity run below query to get all views and tables:

 SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS ObjectName,
    CASE 
        WHEN o.type = 'U' THEN 'Table'
        WHEN o.type = 'V' THEN 'View'
        ELSE 'Other'
    END AS ObjectType
FROM 
    sys.objects o
WHERE 
    o.type IN ('U', 'V') 
    AND SCHEMA_NAME(o.schema_id) = 'dbo' -- Filter for dbo schema
ORDER BY 
    SchemaName, ObjectType, ObjectName;

Lookup output:

Add foreach activity to lookup activity, take @activity('Lookup1').output.value as items with Enabling Sequential option. Add source and sink datasets with two string parameters Schema
and Table define them as @dataset().Schema for schema, @dataset().Table for table. Add them to source, sink of copy activity with values for defined parameters as below:

Schema:@item().SchemaName
table:@item().ObjectName 

Source dataset:

Sink dataset:

After the configuration of copy activity debug the pipeline. All views and tables are copied successfully to the target database as shown below:

Alternatively, to copy all objects of database to the newly created db, open the page for your database, and then choose Copy to open the Create SQL Database - Copy database page. Fill in the values for the target server where you want to copy your database to.

It will copy all objects like tables, stored procedures, views to the target db. For more information you can refer to the MS document.

本文标签：