SQL Server Integration Services (SSIS) Part 16 - Expression and Other Constraints — Transcript

Full transcript

1. 0:00[Music]
2. 0:01Welcome to this short Weisowl tutorial
3. 0:03on using precedence constraints within
4. 0:05SQL Server Integration Services. Here's
5. 0:08what we'll learn in the
6. 0:10tutorial. We'll begin by looking at can
7. 0:13normal constraints. So I'll show you how
8. 0:15to get a task to run depending on the
9. 0:17completion status of the previous
10. 0:19one. We'll then go on to look at
11. 0:21expression constraints. So, I'll show
12. 0:23you how to set expression constraints
13. 0:25and how to switch between them all
14. 0:27having to be true or any one of them
15. 0:29having to be true. So, let's get
16. 0:32[Music]
17. 0:34started. To explain how constraints
18. 0:37work, I've created a package which asks
19. 0:39the question, is cricket boring? I think
20. 0:42we all know the answer to that. There's
21. 0:44two possibilities, yes or no. So, if I
22. 0:46try running that package, you'll see it
23. 0:48comes up with a relevant question using
24. 0:49a script. If I were to answer yes, which
25. 0:52is a correct answer, of course, that
26. 0:54would be deemed to be a success, and I
27. 0:57would go down this green path, and
28. 0:59you'll get a message saying you've
29. 1:00chosen the correct answer. If I answer
30. 1:03no, you'll instead go down the failure
31. 1:05path, and you'll get a message saying
32. 1:07you've chosen the wrong answer. And
33. 1:09whether I answer yes or no, I'll
34. 1:11automatically go down the completion
35. 1:12path because the task completed and I'll
36. 1:15log the fact an answer was
37. 1:16given. So, let's try. Firstly, I'll say
38. 1:19no, it isn't boring. That's the wrong
39. 1:21answer. So, it's displayed that in a
40. 1:23script going down this path. And you can
41. 1:26see the task failed. If I try doing that
42. 1:29again, but this time answer correctly
43. 1:31and say cricket is
44. 1:32boring. Then I've gone down the success
45. 1:35path and got a message saying it's the
46. 1:36right
47. 1:37answer. Whichever I did, whether I chose
48. 1:40yes or no, this log the fact an answer
49. 1:42was given task completed. And in fact,
50. 1:45if I now go to the log file created from
51. 1:47that, which I put here, and double click
52. 1:50on it, you can see the last two times I
53. 1:53ran the task have been logged. It took
54. 1:55me 13 seconds between one and the
55. 1:57other.
56. 1:59So, how did that work? Well, the is
57. 2:02cricket boring script had a message box
58. 2:05within it, which I'll just illustrate
59. 2:07just in case you've never seen this
60. 2:08before.
61. 2:10And what that does is set a variable
62. 2:12called answer to hold the result of a
63. 2:14dialogue box and ask the question is
64. 2:16cricket boring and the configuration
65. 2:19buttons are that you can have a yes and
66. 2:21a no button. If somebody answers yes
67. 2:24then the result is deemed to be a
68. 2:27success and what I did is just copied
69. 2:29and pasted that script to say that
70. 2:30otherwise the result would be deemed to
71. 2:32be a failure. So that's how I got this
72. 2:35to work. What I want to do is how show
73. 2:38now how to configure these
74. 2:39constraints. And to illustrate that,
75. 2:42I've created a simpler version of this,
76. 2:44a template version, which contains all
77. 2:46the same tasks, but not joined
78. 2:48together. What I then did is I dragged
79. 2:51the green success arrow
80. 2:54from my initial question onto the other
81. 2:57three tasks.
82. 2:59I then rightclicked on this one and
83. 3:02chose that this logging task should run
84. 3:05under all possible circumstances that
85. 3:07the previous one completed and the arrow
86. 3:10turns black. I then right clicked on
87. 3:12this other constraint and shows that
88. 3:15this should run if the previous task
89. 3:17failed. So what's happening here is the
90. 3:19script is determining whether it was a
91. 3:21success or failure and that determines
92. 3:23which path we'll go down. And to be
93. 3:26honest, I think that's probably all
94. 3:27there is to say about precedence
95. 3:29constraints. So, we'll now have a look
96. 3:30at expression constraints
97. 3:34instead. To illustrate how expression
98. 3:37constraints work, uh we're going to go
99. 3:39back to our familiar Xactor contestants
100. 3:41workbook. It's based on the popular UK
101. 3:43reality TV show. And we've got three
102. 3:45worksheets. We've got 109 finalists.
103. 3:48We've got nine series. And we've got 10
104. 3:51mentors, the people who advise the
105. 3:53contestants. And what I've done is
106. 3:55created a package which imports this
107. 3:57information into three separate SQL
108. 3:58server tables. So there's 109
109. 4:01contestants. I've got my series table
110. 4:03with nine series and I've got my mentors
111. 4:05table with 10 mentors it. So what
112. 4:08actually does this? If we have a look
113. 4:11integration services here's my package.
114. 4:14What it does firstly is to run an
115. 4:16execute SQL task to get rid of all the
116. 4:18old records. And then it runs three
117. 4:20separate data flow tasks to import the
118. 4:22contestants, series, and mentors at the
119. 4:25same time counting how many records
120. 4:26there are. It stores that information in
121. 4:29three variables which I've created
122. 4:30called number, contestants, number,
123. 4:32mentors, and number series. And finally,
124. 4:34it uses a script to display the results
125. 4:36of those variables. So we can see how
126. 4:38many records we've imported. And then it
127. 4:40has a file system task to copy the
128. 4:42workbook to another
129. 4:43folder. If we have a look at one of
130. 4:45these sample data flow tasks, this is
131. 4:48what it does. It takes a Excel workbook
132. 4:50of contestants for example, counts how
133. 4:53many there are with a row count
134. 4:54transform and then puts the results into
135. 4:57the SQL Server
136. 4:59table. So if I run that, it will all run
137. 5:02completely normally because there's no
138. 5:03constraints in whatsoever. And it says
139. 5:06how many contestants, mentors, and
140. 5:07series I've got. What I'm now going to
141. 5:10do is to add constraints saying that the
142. 5:12final two tasks, these bottom two, will
143. 5:14only run if I have at least 100
144. 5:16contestants. So far so good. at least 10
145. 5:18mentors and at least 10 series. And
146. 5:21because there's only nine, these bottom
147. 5:23two
148. 5:24tasks won't actually run. So let's do
149. 5:27that. To add a constraint, or rather an
150. 5:30expression constraint, you right click
151. 5:31in an existing constraint and choose to
152. 5:33edit it. You can then change it from a
153. 5:36normal constraint to an expression one.
154. 5:39And then you can specify what the
155. 5:40expression should be, which has to be
156. 5:42true for the next task to run. So you
157. 5:45can click on the three dots. And in my
158. 5:46case, I'm going to drag down the number
159. 5:48contestants variable and say that it has
160. 5:50to be greater greater than or equal to
161. 5:53100. If I evaluate that is a bit
162. 5:55misleading because the value of the
163. 5:57variable is currently zero, it will
164. 5:58actually give me false. But when I run
165. 6:01the actual package, this variable will
166. 6:03be populated with 109 and the condition
167. 6:05will be true. So if I choose okay for
168. 6:08that and I'm going to do the same thing
169. 6:09for the other two constraints quite
170. 6:11quickly. So I'm going to turn them both
171. 6:13into expression tasks. This one should
172. 6:15be that the number series has to be
173. 6:17greater than or equal to 10. And the
174. 6:19final one should be that the number of
175. 6:26mentors has to be greater than equal to
176. 6:3110. And you can see I've set
177. 6:33constraints, expression constraints
178. 6:35because of this little symbol appearing
179. 6:36on each of the constraints. If I run
180. 6:38this now, I wouldn't see the message at
181. 6:41the end.
182. 6:42And the reason for that is while two of
183. 6:45these constraints were true, the third
184. 6:47one wasn't. So what we're now going to
185. 6:49do is look at how to make it so these
186. 6:51final two tasks will run if any one of
187. 6:53these constraints is
188. 6:56true. In the previous part of this
189. 6:58tutorial, we created three constraints
190. 7:00which said that the number of
191. 7:01contestants imported has to be at least
192. 7:03100, series has to be at least 10, and
193. 7:06the mentors has to be at least 10. But
194. 7:08the problem is because only two of those
195. 7:10three conditions are true, we're not
196. 7:12running the last two tasks. What I want
197. 7:15to do is show how to edit this so any
198. 7:17one of the conditions can be true. And I
199. 7:19think it's slightly surprising how it
200. 7:21works. What I would expect to have to do
201. 7:23is to select all three constraints at
202. 7:26the same time and right click on them.
203. 7:29But actually, if you do that, it doesn't
204. 7:32help. The slightly surprising way you do
205. 7:34it is just to select any one of them.
206. 7:36And it doesn't actually matter which one
207. 7:38you choose and choose to edit
208. 7:40that. At the bottom where it says
209. 7:42multiple constraints, I'm going to
210. 7:44change it so it's a logical or one
211. 7:46constraint must evaluate to true. If I
212. 7:49then choose okay, you can see that it
213. 7:51changes the line of all three
214. 7:53constraints leading into this task to
215. 7:55dotted which clearly shows that any one
216. 7:57of them can be true and in that case the
217. 8:00last two tasks will run. to see this
218. 8:02working. Now, if I run exactly the same
219. 8:04package as I did in the previous part of
220. 8:06the tutorial, but with a slight change
221. 8:07to the constraints, you can see that
222. 8:09when I choose okay, it's run the script
223. 8:11and copied the file because even though
224. 8:13all three conditions weren't true, at
225. 8:15least one of them
226. 8:17was. And that's the end of this tutorial
227. 8:19on constraints. You can find lots more
228. 8:21resources on SQL Server integration
229. 8:23services, SQL Server.net, and other
230. 8:26Microsoft Office training at
231. 8:30www.wisowl.co.uk. UK.