SQL Server Integration Services (SSIS) Part 18 - Error handling — Transcript

Full transcript

1. 0:00[Music]
2. 0:00welcome to the short wisal tutorial on
3. 0:03error handling in integration Services
4. 0:05here's what we'll cover on the
5. 0:07tutorial I'll begin by showing the
6. 0:09example we're trying to solve so I'll
7. 0:11show what the problem is and then show
8. 0:13this proposed
9. 0:14solution what we'll then do is look at
10. 0:16it recreating the solution in more
11. 0:18detail so I'll show how to use error
12. 0:20handling to redirect errors and then
13. 0:22look at one particular feature of the
14. 0:24configure error output dialog box which
15. 0:26needs further
16. 0:27explanation so let's get started
17. 0:30[Music]
18. 0:33before we look at error handling let's
19. 0:34have a look at the problem I'm trying to
20. 0:36solve I've got a table here called TBL
21. 0:39Great Britains and if we select the rows
22. 0:41in that table you'll see that it
23. 0:42contains 10
24. 0:44rows there was a series in the UK about
25. 0:4710 years ago where people were asked to
26. 0:49vote for their greatest Britains and
27. 0:51this is a result of the poll there's
28. 0:53some quite surprising results I think
29. 0:55it's fair to say that few people would
30. 0:57think Princess Diana deserved to be the
31. 0:59third greatest written of all time I
32. 1:01certainly didn't and I'm not sure people
33. 1:03like John lenon deserve to be there
34. 1:05either and I put the wise how verdict on
35. 1:08the right hand
36. 1:09side what I want to do is to create a
37. 1:11package which will import the data from
38. 1:14this SQL Server table but then try
39. 1:16truncating this verdict to 25
40. 1:19characters six of the records will be
41. 1:21fine but four of them won't for example
42. 1:25you'll never get the word greatest
43. 1:26mathematician ever within 25 characters
44. 1:29and what I'm going to do is create a
45. 1:30package to handle those errors so let's
46. 1:32have a look at
47. 1:35that I've got here the final package in
48. 1:38a moment we'll create this just to show
49. 1:40you where we're trying to go there's a
50. 1:42data flow task to import the SQL Server
51. 1:45table and then I've got a data
52. 1:47conversion tab which will attempt to
53. 1:49truncate the characters and what I'm
54. 1:51going to do is send the good data down
55. 1:53this path and the bow data down this
56. 1:55path and if I just run this package to
57. 1:57show you the results you'll see this is
58. 2:00the first path these are the people
59. 2:01where I didn't have to truncate the data
60. 2:03because the verdict wasn't 25 characters
61. 2:06and if I close down that data view
62. 2:08you'll now see the second one you can
63. 2:09see Isaac Newt Newton greatest
64. 2:11mathematician ever was
65. 2:14truncated and you can see in the final
66. 2:16output six rows went down the okay data
67. 2:19path and four rows went down the
68. 2:21truncated data path and what I'm going
69. 2:24to do now is show you how we achieved
70. 2:28this so two create this package the
71. 2:31first thing I need to do is to add a
72. 2:33data flow task and if I double click on
73. 2:35that within that I'm going to add a
74. 2:38source and what the source will do is
75. 2:40get its information from the SQL Server
76. 2:42table I've got a connection already set
77. 2:44up I'll rename it happily I've got
78. 2:47something already in my clipboard so
79. 2:49it's going to import the allegedly Great
80. 2:51Britains I'll configure that and say
81. 2:53which table is getting its information
82. 2:55from and then what I'm going to do is
83. 2:57feed the information from that into a
84. 2:58data conversion t task and this is a bit
85. 3:01which could go
86. 3:03wrong I'll configure the data conversion
87. 3:05task to take the wial verdict and what
88. 3:08I'm going to do is rename it as
89. 3:11truncated
90. 3:13verdict and I'm going to try to shorten
91. 3:15it to 25
92. 3:17characters now the point behind this
93. 3:19tutorial is what happens now what I'm
94. 3:22going to do is click on this button
95. 3:23which is at the bottom left of many of
96. 3:25the tasks in ssis and configure my error
97. 3:28output there's two types of things I
98. 3:30might want to consider one is what
99. 3:32happens when there's an error and one is
100. 3:34what happens when there's truncation
101. 3:36which is considered as a separate error
102. 3:39it's probably classified internally as a
103. 3:41warning I want to look at truncation and
104. 3:43I've got three choices at the moment
105. 3:45it's just going to fail I could ignore
106. 3:47the fail that sounds a recipe for
107. 3:49disaster what I'm going to do instead is
108. 3:51to redirect the road on a different
109. 3:54pipe I'll look at this bit of the bottom
110. 3:56at the end of this
111. 3:58tutorial if I then choose okay and okay
112. 4:01again I've got a warning there because
113. 4:04at the moment it's saying row sent the
114. 4:06error output will be lost so I need to
115. 4:08put something else in my data
116. 4:10flow so what I'm now going to do is to
117. 4:13add into this I'll just move that down a
118. 4:15bit uh two Union all
119. 4:20transforms to mop up the
120. 4:22data and then I'll direct the good data
121. 4:26the blue arrow into the leftand one and
122. 4:29the bad dat data the error data into the
123. 4:31right hand one when I do that for some
124. 4:34reason it always comes up with this diad
125. 4:36box giving me a chance to change my
126. 4:38choices but I want to keep what I've got
127. 4:39so I'll choose
128. 4:41okay what I'll then do is put a data
129. 4:43viewer on this right hand path so that
130. 4:47when I run the package I'll be able to
131. 4:48see what's going down it and you can see
132. 4:51there the four rows where the data was
133. 4:54truncated so that's how you can
134. 4:56configure error handling Within ssis
135. 5:00[Music]
136. 5:02the last thing I want to do is explain
137. 5:04about a feature in the configure error
138. 5:06output dial box which I mentioned
139. 5:08earlier if I double click on the data
140. 5:10conversion task I can then go into the
141. 5:13configure error output dialog box and
142. 5:16this is a feature I was going to explain
143. 5:18not because it's useful just because
144. 5:19it's so
145. 5:20strange the reason it's there is cuz I
146. 5:23could select several different items in
147. 5:25this list at the top it would make more
148. 5:28sense if I had more columns in in my
149. 5:30list and then I could choose for example
150. 5:33ignore failure and when I click on the
151. 5:35apply button what will happen is it will
152. 5:37change all of these selected items to
153. 5:40the thing I've chosen down
154. 5:41here and that's all that that feature
155. 5:44does presumbly somebody and Microsoft
156. 5:46decided that this was a useful feature
157. 5:48to add but I think it's just plain
158. 5:51confusing and that completes this short
159. 5:53tutorial on error handling in ssis I
160. 5:57hope you've enjoyed this short tutorial
161. 5:58on error handling in
162. 6:00ssis you can find lore training
163. 6:03resources and all things SQL server.net
164. 6:05or Microsoft Office at
165. 6:08www.wis .co.uk
166. 6:11[Music]