-
Notifications
You must be signed in to change notification settings - Fork 0
/
output_sample.sql
153 lines (121 loc) · 5.28 KB
/
output_sample.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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
/* Precedence Constraint(실행 순서)
[Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task1] --> [Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task2]
[Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task2] --> [Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task3]
[Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task3] --> [Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task4]
[Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task4] --> [Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task5]
*/
/******************************************************************************************************/
/* Connections(연결 정보)
1. key 캐시연결
- CreationName:CACHE
- ConnectionString:[]
2. 10.x.x.1.admin.admin
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.1;User ID=admin;Initial Catalog=admin;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]
3. 10.x.x.1.db3.admin
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.1;User ID=admin;Initial Catalog=db3;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]
4. 10.x.x.1.db3.admin
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.1;Initial Catalog=db4;Provider=SQLNCLI11;Integrated Security=SSPI;]
5. localhost.db2
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.2;User ID=admin;Initial Catalog=db2;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]
6. localhost.db1
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.3;User ID=admin;Initial Catalog=db1;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]
*/
/******************************************************************************************************/
/* [Data Flow(데이터 흐름) TaskName: Package\update\task4 Source]
[Connection: Package.ConnectionManagers[10.x.x.4.db6.admin]] */
select ...
from ...
/******************************************************************************************************/
/* [Data Flow(데이터 흐름) TaskName: Package\update\task4 Target]
[Connection: Package.ConnectionManagers[localhost.db1]] */
OpenRowset: [dbo].[table1]
/******************************************************************************************************/
/* [Control Flow(제어 흐름) TaskName: Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task1]
[Connection: 6. localhost.db1
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.3;User ID=admin;Initial Catalog=db1;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]]
*/
delete from ... where dt = @dt
insert ...
(...)
select
...
from (
select
...
from #tempp a
inner join ... b
on ...
where (...)
) t
where ...
/******************************************************************************************************/
/* [Control Flow(제어 흐름) TaskName: Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task2]
[Connection: 5. localhost.db2
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.2;User ID=admin;Initial Catalog=db2;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]]
*/
if object_id('tempdb.dbo.#tempp') is not null
drop table #tempp
select
...
into #tempp
from ... a
where dt = @dt
group by ...
merge ... a
using (...) b
on ...
when not matched then
insert(...)
values(...);
/******************************************************************************************************/
/* [Control Flow(제어 흐름) TaskName: Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task3]
[Connection: 3. localhost.db3
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.1;User ID=admin;Initial Catalog=db3;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]]
*/
update b
set ...
from ... a
inner join ... b
on ...
where ...
/******************************************************************************************************/
/* [Control Flow(제어 흐름) TaskName: Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task4]
[Connection: 4. localhost.db4
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.1;Initial Catalog=db4;Provider=SQLNCLI11;Integrated Security=SSPI;]]
*/
update b
set ...
from ... a
inner join ... b
on ...
where ...
/******************************************************************************************************/
/* [Control Flow(제어 흐름) TaskName: Package\Foreach 루프 컨테이너\시퀀스 컨테이너\task5]
[Connection: 2. localhost.admin
- CreationName:OLEDB
- ConnectionString:[Data Source=10.x.x.1;User ID=admin;Initial Catalog=admin;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;]]
*/
if object_id('tempdb.dbo.#tempm') is not null
drop table #tempm
select
...
into #tempm
from a
where a.dt = @dt
merge ... a
using (...) b
on ...
and ...
when not matched then
insert (...)
values (...);
/******************************************************************************************************/