SQL Server Integration Services (SSIS) Part 8 - Debugging — Transcript

Full transcript

1. 0:00welcome to this wiow tutorial on
2. 0:02debugging and SQL Server integration
3. 0:04Services here's what you'll learn in the
4. 0:07tutorial we'll Begin by looking at how
5. 0:09to solve runtime errors so I'll show how
6. 0:12to set up an example package and we'll
7. 0:14look at how to solve a genuine runtime
8. 0:17error using the output
9. 0:18window we'll then look at how to debug
10. 0:21data flow tasks using data
11. 0:23viewers and finally we'll look at how to
12. 0:25debug control flow tasks by setting
13. 0:28break points and then by by using the
14. 0:30watch and the locals and other windows
15. 0:32to monitor the value of variables so
16. 0:35let's get
17. 0:37started before we start looking at
18. 0:39debugging in ssis let's take a moment
19. 0:42just to see which example we're going to
20. 0:44be using I've got here a workbook of
21. 0:47xact contestants from the UK version of
22. 0:49the show anybody who's been watching the
23. 0:51other videos in this series will
24. 0:53probably be familiar with this by now
25. 0:55and if I scroll down you can see there's
26. 0:58actually 109 rows in the
27. 1:01spreadsheet now what I'm going to do is
28. 1:04import this into integration services
29. 1:07and display how many rows there are so
30. 1:09if I run my package which I've already
31. 1:11created you can see it displays a
32. 1:13message saying the number of rows
33. 1:15imported is
34. 1:17109 here's how this works I've got two
35. 1:21control flow tasks the first is a
36. 1:24responsible for importing the Excel
37. 1:25contestants into a variable so if I
38. 1:28double click on that you can you can see
39. 1:30it cons consists of a source based on an
40. 1:33Excel workbook and then a rowon
41. 1:36transform which stores the information
42. 1:38the number of rows imported in a
43. 1:40variable called number
44. 1:42rows my second control flow task is a
45. 1:45script which passes in the number of
46. 1:47rows variables into the script and then
47. 1:50uses C in this case to display a message
48. 1:54saying how many rows have been found and
49. 1:57there's the value of the variable which
50. 1:58is being cons pated with other bits of
51. 2:00text to form the
52. 2:04message so that's what my package is
53. 2:06doing and as I said when I run it
54. 2:08everything seems to be going alarmingly
55. 2:10well for
56. 2:14now in the previous part of this
57. 2:16tutorial I showed you the example we
58. 2:18were going to be using but I wasn't
59. 2:20quite honest with you I made out that
60. 2:22the first time I ran it it work
61. 2:24perfectly but that sadly wasn't the case
62. 2:26this is what actually happened the first
63. 2:28time I ran it although it appeared to be
64. 2:31working perfectly I couldn't see the
65. 2:33message box at the end and when I went
66. 2:35to the data flow I could see that it had
67. 2:37stumbled on the Excel source so what was
68. 2:40going
69. 2:42wrong if you've got a problem like this
70. 2:44and you can't work out where your
71. 2:46package is going wrong a good place to
72. 2:48look is the output window now I've got
73. 2:51mine visible down there but in the
74. 2:53unlikely event you can't see it you can
75. 2:55get to it by choosing View for the menu
76. 2:57and choosing output but I don't need to
77. 3:00do that cuz as I say mine is visible and
78. 3:02if I bring that up you can see that the
79. 3:04problem has happened shortly after the
80. 3:06validation phase the requested olb
81. 3:10provider is not registered if the 64-bit
82. 3:13drive is not installed run the package
83. 3:15in 32-bit
84. 3:17mode so what I did is I Googled some
85. 3:20combination of those words and I quickly
86. 3:22found that I was by no means the only
87. 3:24person with the same problem if you're
88. 3:26running an application running
89. 3:27integration services on a 64 machine
90. 3:30you'll probably run into this problem
91. 3:32too the problem is that when you deploy
92. 3:34the application it will run perfectly in
93. 3:36the operating system but when you're
94. 3:38designing the application it runs in
95. 3:4032-bit mode and there's no 64-bit jet
96. 3:43driver for Excel or
97. 3:45access so the solution is to right click
98. 3:49on your package choose the
99. 3:52properties and change it so that in
100. 3:56debugging it's
101. 3:58using it's rather not using 64-bit
102. 4:01runtime so if I choose that and then
103. 4:04stop debugging and then start again
104. 4:07you'll see this time it works absolutely
105. 4:10perfectly now my point behind showing
106. 4:12you that isn't to show off about the
107. 4:14fact I know about 32 and 64-bit
108. 4:16applications I'm not an expert to be
109. 4:18honest more to show you that you can use
110. 4:21the output window to keep track of
111. 4:22what's going on in your
112. 4:27application there's two ways you can
113. 4:29find problem s in ssis packages one is
114. 4:32by debugging the control flow to check
115. 4:34the order in which commands are
116. 4:35executing and that's what we'll do in
117. 4:37the next part of this tutorial what
118. 4:39we'll do in this part is have a look at
119. 4:40the data flow to check if data's flowing
120. 4:43down the system as we expect it to be
121. 4:46and the way to do that is to rightclick
122. 4:48on any pipe of data and choose to enable
123. 4:51something called a data viewer which we
124. 4:53looked at briefly in an earlier
125. 4:55tutorial you can see a little symbol
126. 4:57pops up there and I can write click on
127. 5:00that and choose to edit it to see what's
128. 5:02actually going
129. 5:03on if I click on the metadata tab it
130. 5:06gives me a description or a list of all
131. 5:08of the columns which are coming out of
132. 5:10Excel and one useful feature is that I
133. 5:12can copy that of the clipboard and
134. 5:14perhaps paste it into Excel or some
135. 5:16other application for further
136. 5:20analysis what I can also do is see which
137. 5:22columns are going to be listed in my
138. 5:24data viewer now I'm going to click on
139. 5:26this button to transfer them all over to
140. 5:28the left hand side and instead just
141. 5:30decide to display the name of the
142. 5:32contestant and the series in which
143. 5:34they're competing and then choose
144. 5:37okay what will happen now when I run the
145. 5:40application is that it will pause at the
146. 5:42data viewer and that will come up in a
147. 5:44separate window and again I could copy
148. 5:47this data if I wanted to to a separate
149. 5:51spreadsheet again presumably so that I
150. 5:54can analyze it further to check it's
151. 5:56doing what I think it should be doing
152. 5:58but I'm not going to do that I'm just
153. 5:59going to carry on running the
154. 6:01application and you can see it's now
155. 6:02resumed where it left off and it's
156. 6:05actually displaying the message and if I
157. 6:09press all tab with a bit of luck I can
158. 6:10find that message it tends to be a bit
159. 6:13tricky to get to and then choose okay to
160. 6:15clear
161. 6:17it when I finish with my data
162. 6:19viewer I can just right click on it and
163. 6:23choose to disable it you need to be
164. 6:26careful not to delete it because what
165. 6:28that will actually do is delete the ENT
166. 6:29entire pipe of data so if I disable the
167. 6:31data viewer it vanishes from View and
168. 6:34will no longer be executed when I run
169. 6:36the
170. 6:38package in the previous part of this
171. 6:40tutorial we had a look at data flow
172. 6:42tasks and data viewers but what's the
173. 6:44equivalent way of controlling what's
174. 6:46happening within a control flow task as
175. 6:49it turns out the answer is to right
176. 6:51click on the control flow task and
177. 6:53choose to edit
178. 6:54breakpoints and when you do that
179. 6:56integration Services gives you a list of
180. 6:57all of the events which can happen in
181. 6:59this t asks life the two main ones I can
182. 7:03begin the onpreexecute event happens
183. 7:06just before that happens and it can end
184. 7:08at which point the on post execute event
185. 7:10will happen so I'm going to set a break
186. 7:13point for both of those two so that I
187. 7:15can see when the task begins and when it
188. 7:17ends for any breakpoint you can also set
189. 7:20a hit count type this is only really
190. 7:22relevant when you've got some sort of
191. 7:24loop occurring so for example you might
192. 7:27want to you might be looping over files
193. 7:29on your hard disk and you might want to
194. 7:31set a counter that after the 10th file
195. 7:33is um loaded in then a breakpoint will
196. 7:37uh occur and you'll go into debug mode
197. 7:39but we'll keep that as always for both
198. 7:41the two
199. 7:42breakpoints if you then choose okay I
200. 7:45can now run my package but just before I
201. 7:47do that can I show you what the debug
202. 7:49menu looks like at the moment if I
203. 7:51choose debug from the menu and choose
204. 7:52Windows you'll see there's only three
205. 7:54options on that menu that's going to
206. 7:56change quite radically in a second so if
207. 7:59I now run my application by choosing to
208. 8:01start debugging it will execute until it
209. 8:04hits the first break point and you can
210. 8:06see it's at a breakpoint because there's
211. 8:08a little yellow arrow on the breakpoint
212. 8:10symbol another way to tell what's
213. 8:12happened is to go to the output window
214. 8:15and you can see that it's broken when
215. 8:17the container receives the on pre
216. 8:19execute event it's not exactly friendly
217. 8:21but it's pretty obvious what's going on
218. 8:24going back to my point about the debug
219. 8:25window now you can see I've got loads
220. 8:28and loads of Windows there available to
221. 8:30me because I'm now in debugging mode and
222. 8:31I've hit a breakpoint but I'm going to
223. 8:34ignore all of them for the moment and
224. 8:35just continue executing and what you can
225. 8:38now see is on the output window I've
226. 8:41reached the post execute
227. 8:43event and if I carry on executing this
228. 8:47it will run and finish a task and finish
229. 8:49in fact the entire package because
230. 8:51there's no more break points so I can
231. 8:53now find my message and choose okay to
232. 8:56clear it and then stop debugging
233. 9:00so that's how you can set break points
234. 9:01for a task just right click and choose
235. 9:03edit break points but at the moment to
236. 9:06be honest they're not a great deal of
237. 9:07use so what we'll now have a look is how
238. 9:10you can look at the value of variables
239. 9:11to see what's actually going on within a
240. 9:13control flow
241. 9:16task we've just created two break points
242. 9:20for this control flow task one to be hit
243. 9:23when it begins and one to be hit when it
244. 9:24ends what I now want to see is how the
245. 9:26value of the variable number rows
246. 9:28changes during these break points so to
247. 9:31do that I'm going to start debugging my
248. 9:33package and then what I'm going to do
249. 9:35firstly is look at the locals window
250. 9:39whoops so the locals window allows me to
251. 9:42display the value of all variables in my
252. 9:44package so I can click on the plus next
253. 9:46to variables to expand that section but
254. 9:48unfortunately I see not only my user
255. 9:50ones but also the system ones and it's
256. 9:52quite difficult to locate the value of
257. 9:54my variable called number rows but now
258. 9:57that I found it you can see its current
259. 9:58value is
260. 10:00zero so I'm not a big fan of the locals
261. 10:02window because I find it a bit confusing
262. 10:04because everything's muddled up together
263. 10:07so what I'm going to do instead is to
264. 10:08set a watch and you can do that by
265. 10:12choosing debug from the menu choosing
266. 10:14windows and choosing to create one of
267. 10:16four watch windows and because I haven't
268. 10:18got any at the moment I'll put
269. 10:20everything in watch window number one
270. 10:23now I want to look at the value of the
271. 10:24variable called number rows so what I
272. 10:26can do is display the variables window
273. 10:28by clicking on the symbol
274. 10:30and then drag my number rows variable
275. 10:33onto my watch window and release the
276. 10:35mouse button you can see the mouse Point
277. 10:37has a Plus on to show I'm copying
278. 10:39something so now I'm watching the value
279. 10:41of that variable and I could add others
280. 10:43if I had any in my package when I run my
281. 10:46application
282. 10:47now you can see the value of that
283. 10:49variable has just changed from 0 to 109
284. 10:52so the watch window provides a way to
285. 10:54monitor variables to see when their
286. 10:56value
287. 10:58changes in in addition to the watch
288. 11:00window I'll just delete that watch
289. 11:03there's also something called quick
290. 11:05watch and you can get to that either by
291. 11:08pressing shift F9 or by choosing quick
292. 11:11watch what that allows you to do is to
293. 11:14click on the drop Arrow or type in an
294. 11:17expression and quickly evaluate the
295. 11:19value of that and you can also add it to
296. 11:22your watch window in which case it's
297. 11:24just another way to watch the value of
298. 11:26the
299. 11:27variable now there's lots of other
300. 11:29things available to you when debugging
301. 11:31ssis packages but to be honest the ones
302. 11:34I've just shown you are the ones which I
303. 11:35find most useful and some of these I
304. 11:38think will only be used by the very most
305. 11:40advanced system
306. 11:42programmers so I'll just stop debugging
307. 11:45and that completes this look at how to
308. 11:47debug packages in
309. 11:50ssis you can find lots more training
310. 11:52resources on Microsoft applications
311. 11:54online at www.wow.com