SQL Server Integration Services (SSIS) Part 5 - Data Types and Data Conversion — Transcript

Full transcript

1. 0:00welcome to this wiow tutorial on SQL
2. 0:03Server integration Services data types
3. 0:05and data conversion
4. 0:08Transformations here's what you'll learn
5. 0:10on the
6. 0:11tutorial we'll Begin by having a look at
7. 0:13why data types matter proving that when
8. 0:15you try to export information from Excel
9. 0:17to SQL Server things can go horribly
10. 0:21wrong we'll then look at using data
11. 0:23conversion transformations to solve
12. 0:25problems like this and then finally I'll
13. 0:27have a look at the list of the main ssis
14. 0:30data
15. 0:31types so let's get
16. 0:35started okay so let's look at why data
17. 0:37types matter in SQL Server integration
18. 0:40Services I've got here a simple
19. 0:42spreadsheet of all the SQL Server
20. 0:43courses wise I'll give I thought i'
21. 0:45throw in a quick bit of publicity there
22. 0:47and the column I want you to look at is
23. 0:49a course name one which contains a text
24. 0:51description of what the course is called
25. 0:54and what I'm going to do is export that
26. 0:56into a SQL Server table called TBL
27. 0:59course
28. 1:00and it contains the same three columns
29. 1:03course ID course name and C number days
30. 1:05and again I want you to look at the
31. 1:07course name so here's my package to do
32. 1:10that already set up uh beginning with
33. 1:12the control flow I've got a um execute
34. 1:16SQL task which will delete all the old
35. 1:18courses from the table ready to start
36. 1:21again and then I've got a single data
37. 1:23flow task which will import all the
38. 1:25courses from Excel into the new SQL
39. 1:27Server table so if we have a look at
40. 1:29that in more detail
41. 1:30what it has is an Excel source which is
42. 1:32a workbook I've just shown and a
43. 1:35destination which is a table of courses
44. 1:37so like I say what could possibly go
45. 1:40wrong well one thing which could
46. 1:42definitely go wrong is I could leave the
47. 1:44Excel workbook open which is something
48. 1:46I'm prone to do so I'm going to close
49. 1:47that down but the second thing which is
50. 1:50going to go wrong is shown by this Red
51. 1:52Cross it can't convert between Unicode
52. 1:55and non-unicode string data
53. 1:57types and the reason for that is by
54. 2:00default XO Excel holds its information
55. 2:03in Unicode format so it sets aside twice
56. 2:07as many bytes to hold each character as
57. 2:09the SQL Server so if we look in the
58. 2:13Excel workbook in more detail I'm going
59. 2:16to do that not by double clicking on it
60. 2:18but by right clicking and choosing to
61. 2:20show the advanced
62. 2:22editor so you can see on the last tab of
63. 2:25this which appears the input and output
64. 2:28properties
65. 2:30and you can see here if I go to my
66. 2:32output
67. 2:33columns that the course name is being
68. 2:37created using a dtore wst data type or
69. 2:43Unicode string now towards the end of
70. 2:46this tutorial I'll talk much more in
71. 2:49much more detail about all the various
72. 2:50data types and integration services for
73. 2:53the moment DT stands for data
74. 2:55transformation the old name for
75. 2:57ssis um I have no idea what the w stands
76. 3:00for and St Str stands for
77. 3:02string so that's what Excel is
78. 3:05exporting here's what SQL Server is
79. 3:08importing because I've chosen varar it
80. 3:11thinks this is a non-unicode data type
81. 3:13so I've only got two bytes set aside for
82. 3:16each character rather than four as it
83. 3:19happens had I just put an n in front of
84. 3:22it it would have created it as an nvar
85. 3:24car data type which is Unicode and I
86. 3:27wouldn't have had this problem but then
87. 3:29again I wouldn't been able to illustrate
88. 3:30how to do a data type conversion either
89. 3:33so what I need to do is between my Excel
90. 3:37source and my SQL Server destination I
91. 3:40need to add in a transformation which
92. 3:44which will convert the data type of the
93. 3:46course name column into the correct
94. 3:48format so what I'm going to do is break
95. 3:50the link
96. 3:51there and then add a transformation
97. 3:54between the two parts of the data flow
98. 3:58task so to add a data conversion task I
99. 4:02need to find it in the list of common
100. 4:05transformations in the ssis toolbox and
101. 4:08drag it onto my data flow window what I
102. 4:11can then do is rename it so at the
103. 4:13moment it's called data conversion I'll
104. 4:15call it change course name column
105. 4:18because that's what it's going to do um
106. 4:21and then what I can do is to
107. 4:23drag the output from the Excel Source
108. 4:27into my data conversion transformation
109. 4:30what I then need to do of course is
110. 4:31configure my data conversion to say what
111. 4:33it actually does and I can do that as
112. 4:35usual by double clicking on this
113. 4:37icon now there's no need for me to tick
114. 4:40all three columns because the course ID
115. 4:43and the course number days aren't being
116. 4:45affected so what I'm going to do is
117. 4:47include the course
118. 4:48name and as always in ssis it makes it
119. 4:51as clear as it possibly can what I need
120. 4:53to do so the first thing I need to do is
121. 4:55to give this an output Alias so from now
122. 4:58on it's going to be called shorter
123. 5:01course name perhaps not a great
124. 5:03description but it will serve for the
125. 5:05purpose the data type and this is a
126. 5:07crucial thing is not going to be a
127. 5:09Unicode string it's just going to be a
128. 5:12string so dtore St and that will
129. 5:16correspond to a varar data type in SQL
130. 5:20Server now at the moment I'm going to
131. 5:22get conversion errors or rather
132. 5:24truncation errors because I've set the
133. 5:25length to 255 characters so what I'm
134. 5:28going to do is abbreviate it to let's
135. 5:31say 50
136. 5:32characters and what that will do is
137. 5:34automatically trate it the final thing
138. 5:37I've got on this line is a code page
139. 5:40which if you're working in the UK or the
140. 5:42US will always be 1252 but I can click
141. 5:45on the drop arrow and decide to work in
142. 5:47somewhere far more exotic and
143. 5:48interesting instead but I'm not going to
144. 5:50do that because I'm not in an exotic or
145. 5:52interesting
146. 5:53place so finally I can choose okay to
147. 5:56confirm that and I've completed
148. 5:58configuring my my data
149. 6:01conversion what I can now do is take the
150. 6:03output of that and feed it into my final
151. 6:06SQL Server
152. 6:08destination and then I can configure my
153. 6:11SQL Server destination by double
154. 6:13clicking on it and what I need to do is
155. 6:15change the mappings change what columns
156. 6:18on the from the input are going to what
157. 6:20columns in the
158. 6:21destination you can see the course name
159. 6:24is still going onto the course name so
160. 6:26what I can do is just click on the input
161. 6:27column and change it so that the thing
162. 6:29which is mapped onto the SQL Server
163. 6:31course name column is actually my new
164. 6:33shorter converted column called shorter
165. 6:36course name and then I can choose
166. 6:39okay now I've already set that up to me
167. 6:42to be my default package so when I
168. 6:44execute this what I should do is I
169. 6:47should see three green ticks as my data
170. 6:50flows um without let or hindrance from
171. 6:54my source to my destination taking a wee
172. 6:57while to do it but there it is and if I
173. 6:59I now go into SQL server and right click
174. 7:01on my TBL course table and choose to
175. 7:03select all the rows you should see that
176. 7:06I've successfully imported all the
177. 7:08courses from the Excel
178. 7:11workbook so here's a list of the data
179. 7:14types in SQL Server integration services
180. 7:17on the left hand side we have the SQL
181. 7:18Server data type and on the right hand
182. 7:21side the corresponding SQL Server
183. 7:23integration Services data type so you
184. 7:25can see there confirmation that for
185. 7:27string data types VAR car in SQL Server
186. 7:30corresponds to dtor St but the Unicode
187. 7:34version of that nvar car corresponds to
188. 7:36dtore
189. 7:38wstr moving on now to integer and
190. 7:41Boolean data types the main one you'll
191. 7:43need there is dtore I4 which corresponds
192. 7:47to a 4 byte integer or INT in SQL server
193. 7:51for floating Point data types you can
194. 7:53use dtore R4 or dtore R8 corresponding
195. 7:58to real or float or dtore numeric for
196. 8:01decimal or
197. 8:02numeric for date and time data types
198. 8:05there's a list
199. 8:06there and for other data types for
200. 8:09completeness I've included a list for
201. 8:11that too I hope you've enjoyed this
202. 8:13short tutorial on data types and data
203. 8:15conversion Transformations you can find
204. 8:18lots more online training resources on
205. 8:20SQL server.net and Microsoft Office
206. 8:23applications at
207. 8:25www.wis
208. 8:28al.co