CODEWITHSUNDEEP
azure-data-factory
Patterson
Patterson

Reputation: 2815

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

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:

enter image description here

And for the sink I have the following: enter image description here

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" enter image description here

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'.

Upvotes: 0

Views: 314

Answers (1)

Bhavani
Bhavani

Reputation: 5317

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:

enter image description here

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:

enter image description here

Sink dataset:

enter image description here

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

enter image description here

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.

enter image description here

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

Upvotes: 1

Related Questions