-
Notifications
You must be signed in to change notification settings - Fork 0
/
usp_insert_metadata.sql
126 lines (114 loc) · 4.1 KB
/
usp_insert_metadata.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
CREATE PROCEDURE [dbo].[usp_insert_metadata](@serverName NVARCHAR(50) ,
@databaseName NVARCHAR(50),
@schemaName NVARCHAR(50),
@tableName NVARCHAR(50),
@fileSystemName NVARCHAR(50),
@rootFolderPath NVARCHAR(50),
@topLevelPipelineName NVARCHAR(50) ,
@metadataControleTable nvarchar(100)
)
AS
BEGIN
-- DECLARE
-- @serverName NVARCHAR(50) = 'testServer',
-- @databaseName NVARCHAR(50) = 'testDB',
-- @schemaName NVARCHAR(50) = 'testShema',
-- @tableName NVARCHAR(50) = 'testTable',
-- @fileSystemName NVARCHAR(50) = 'testFilesystem',
-- @topLevelPipelineName NVARCHAR(50) = 'MetadataDrivenCopyTask_TopLevel',
-- @rootFolderPath NVARCHAR(50) = 'testRoot',
-- @metadataControleTable nvarchar(100)= 'MainControlTable'
SET NOCOUNT ON;
CREATE TABLE [dbo].[#MainControlTable] (
[SourceObjectSettings] NVARCHAR (MAX) NULL,
[SourceConnectionSettingsName] VARCHAR (MAX) NULL,
[CopySourceSettings] NVARCHAR (MAX) NULL,
[SinkObjectSettings] NVARCHAR (MAX) NULL,
[SinkConnectionSettingsName] VARCHAR (MAX) NULL,
[CopySinkSettings] NVARCHAR (MAX) NULL,
[CopyActivitySettings] NVARCHAR (MAX) NULL,
[TopLevelPipelineName] VARCHAR (MAX) NULL,
[TriggerName] NVARCHAR (MAX) NULL,
[DataLoadingBehaviorSettings] NVARCHAR (MAX) NULL,
[TaskId] INT NULL,
[CopyEnabled] BIT NULL
);
DECLARE @MainControlMetadata NVARCHAR(max) = N'{
"SourceObjectSettings": {
"schema": "'+ @schemaName + N'",
"table": "'+ @tableName + N'"
},
"SinkObjectSettings": {
"fileName": "'+ @schemaName + @tableName + N'.csv",
"folderPath": "'+ @rootFolderPath + N'/'+ @serverName + N'/'+ @databaseName + N'",
"fileSystem": "'+ @fileSystemName + N'"
},
"CopySourceSettings": {
"partitionOption": "None",
"sqlReaderQuery": null,
"partitionLowerBound": null,
"partitionUpperBound": null,
"partitionColumnName": null,
"partitionNames": null
},
"CopyActivitySettings": {
"translator": null
},
"TopLevelPipelineName": "'+ @topLevelPipelineName + N'",
"TriggerName": [
"Sandbox",
"Manual",
"Trigger"
],
"DataLoadingBehaviorSettings": {
"dataLoadingBehavior": "FullLoad"
},
"TaskId": 0,
"CopyEnabled": 1
}'
INSERT INTO [dbo].[#MainControlTable] (
[SourceObjectSettings],
[SourceConnectionSettingsName],
[CopySourceSettings],
[SinkObjectSettings],
[SinkConnectionSettingsName],
[CopySinkSettings],
[CopyActivitySettings],
[TopLevelPipelineName],
[TriggerName],
[DataLoadingBehaviorSettings],
[TaskId],
[CopyEnabled])
SELECT * FROM OPENJSON(@MainControlMetadata)
WITH ([SourceObjectSettings] [nvarchar](max) AS JSON,
[SourceConnectionSettingsName] [varchar](max),
[CopySourceSettings] [nvarchar](max) AS JSON,
[SinkObjectSettings] [nvarchar](max) AS JSON,
[SinkConnectionSettingsName] [varchar](max),
[CopySinkSettings] [nvarchar](max) AS JSON,
[CopyActivitySettings] [nvarchar](max) AS JSON,
[TopLevelPipelineName] [varchar](max),
[TriggerName] [nvarchar](max) AS JSON,
[DataLoadingBehaviorSettings] [nvarchar](max) AS JSON,
[TaskId] [int],
[CopyEnabled] [bit]);
DECLARE @sql NVARCHAR(max) = '
INSERT INTO [dbo].['+ @metadataControleTable +'] (
[SourceObjectSettings],
[SourceConnectionSettingsName],
[CopySourceSettings],
[SinkObjectSettings],
[SinkConnectionSettingsName],
[CopySinkSettings],
[CopyActivitySettings],
[TopLevelPipelineName],
[TriggerName],
[DataLoadingBehaviorSettings],
[TaskId],
[CopyEnabled])
SELECT * FROM #MainControlTable
'
exec(@sql)
RETURN 0
END;
GO