Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012

Errata for Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012




The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed, PDF, ePub, Mobi, Safari Books Online, Other Digital Version
Page xxix
url

Note About Companion Content & Sample Databases All of the companion content and sample databases are freely available here for download: http://go.microsoft.com/FWLink/?Linkid=260986 If you cannot access the files, please contact our Customer Service department at msinput@microsoft.com. An earlier version of the companion content web page contained an incorrect URL, which is now fixed. You may need to refresh your browser's cache before the new page will load for you. We are sorry about any confusion caused by our error.

AdamZ  Jan 15, 2013 
Printed
Page xxix
Source Code

The specified link for the source code zip TK70463_CodeLabSolutions.zip is displayed on front page but the link does not provide access to the file, just access to the sample databases.

Note from the Author or Editor:
O'Reilly is informed about this issue and it should be resolved soon. Thank you for reporting. Dejan Sarka You should be able to download the companion content from http://examples.oreilly.com/9780735666092-files/. Thank you for your patience, Dejan Sarka

Anonymous  Dec 22, 2012  Jan 11, 2013
Other Digital Version
Practice Test for 70-463
Question: 463P-2.4_02C

This question "463P-2.4_02C" has an incorrect answer. The "Merge Join" transformation is partial blocking yet when I add it to the 'Partial blocking' node on the transformation tree as an answer it marks it as incorrect. The explanation states: The transformation types are: * Non-blocking: * Multicast * Derived Column * Row Count * Export Column Partial blocking: * Union All Blocking: * Aggregate * Sort * Fuzzy Lookup It should be: The transformation types are: * Non-blocking: * Multicast * Derived Column * Row Count * Export Column Partial blocking: * Union All * Merge Join Blocking: * Aggregate * Sort * Fuzzy Lookup

Note from the Author or Editor:
Merge join is partly blocking. Please inform the practice test questions author about this issue. Dejan Sarka

Anonymous  Apr 22, 2013 
PDF
Page Practice test Question Regarding "activities perfomed to improve and maintain data quality"
Practice test

On the practice test there is a question 'You are part of a team setting up Master Data Management by using SQL Server Manager Data Services within your organization. As part of a kickoff meeting, the team is identifying the roles and processes that will be addressed. Which of the following activities refers to "activities performed to improve and maintain data quality"? It says that the answer 'Data Stewardship' is INCORRECT because 'data stewards are the people responsible for these activities.' First the answer choice is 'Stewardship' not 'Stewards' 'Stewards' is a noun and 'Stewardship' is a verb. The definition of stewardship is 'the conducting, supervising, or managing of something; especially : the careful and responsible management of something entrusted to one's care <stewardship of natural resources>' Thus 'STEWARDSHIP' is the activity of performing 'Master Data Management'. and therefore is 'Master Data Management'. The answer 'Stewardship' is correct.

Note from the Author or Editor:
Again, I can only confirm that this is an error. Data Stewardship is definitely a process that will be addressed. Dejan Sarka

Arthur Hill  Dec 09, 2013 
PDF
Page Practice test
Question regarding when to parameterize or not

One of the practice test questions says 'You are designing an SSIS solution, deciding which of your variables to parameterize and which to determine automatically at runtime. Which of the following items should be determined automatically rather than parameterized' One of the answers is 'Data source queries' Excuse my ignorance, but what is your definition of a 'data source query' ? I have searched the Microsoft.com site for the phrase and there is only one time it is used and it is in a title to an article. Interestingly enough, a search of your book for the phrase 'data source query' returns NO RESULTS. Could you please give an example of what you mean by this phrase. Are you talking about creating Dynamic SQL statements based on the environment under which the package is being executed? If so, should not the phrase be 'Dynamic SQL based queries' or some variant thereof? I have been working with SQL since INFORMIX was in it's infancy and have never used this phrase. Yes I am an old guy.

Note from the Author or Editor:
My best guess here is that the author of the practice test question means using the SQL Command data access mode for some OLE DB source. I guess that the term "data source query" means "SQL Command". SQL Command access mode is mentioned on page 182. Dejan Sarka

Arthur Hill  Dec 09, 2013 
PDF
Page Practice test
Question regarding 'degenerate dimension'

A question on the test states 'Analysts have requested the ability to group sales transactions by batch number, which is an arbitrary number assigned by the line-of-business system as it processes sales. There are no other attributes relating to batch number. The existing fact table currently has six foreign keys (relating to Date, Customer, Product, Channel, Employee, and Promotion). What is the most efficient way to modify the star schema to enable this analysis?' The correct answer is 'Add the batch number as a degenerate dimension to the fact table.' I searched the text of the book and there is no mention of the term 'degenerate dimension' whatsoever. I would suggest that if a term is important enough to be the answer to a test question, it should, at a minimum, receive a cursory explanation in the text.

Note from the Author or Editor:
The topic "Many-to-Many Relationships" in chapter 1, pages 30 to 32, talks about generating such a dimension, a dimension which consist only of the key column(s) of the fact table. In Kimball's terminology, this is a "degenerated" dimension. MS terminology does not use this term, and therefore I didn't use it. However, I am not the author of the practice test questions. IMHO the author should read the book and use the book terminology. Dejan Sarka

Arthur Hill  Dec 09, 2013 
PDF
Page Practice test question 130
Test question

A question on the practice test says 'You have developed an SSIS package, and tested it successfully in SSDT. You have deployed the package to the Integration Services catalog. However, when you attempt to execute the package from the SSMS SQL Server Agent node in Object Explorer, the package fails.' In the Explanations section it states 'You cannot attempt to execute the package from the SSMS SQL Server Agent node in Object Explorer when SQL Server Agent is not started; the node will not expand.' I am running SQL Server 2012 Developer on a Win 8.1 Pro box. SQL Server Agent IS NOT STARTED - Checked in Services The SQL Server Agent Node, which displays the not running icon, expands and exposes all the jobs including the jobs set up to run the SSIS packages.

Note from the Author or Editor:
I can only confirm this, I also tested it, and got the same behavior. Dejan Sarka

Arthur Hill  Dec 11, 2013 
PDF
Page Practice test
Practice test question 137

Question reads: 'SSIS offers many powerful control flow tasks out of the box. However, some situations require development of Script tasks or custom control flow tasks, depending on whether the component will be reused across packages. Assuming that your need is to be able to use a task many times, which of the following scenarios could require development of a custom task? (Each correct answer presents a complete solution. Choose three.)' One of the correct answers is: 'Execute and render an SSRS report.' First of all, what do you mean by 'render'? Surely you are not talking about rendering a report to the UI of a server, which probably does not have a UI. I can not see any reason why you would use a custom control flow for this purpose. It took less than 1 minute to find a dozen ways to 'Execute and render a SSRS report' by using standard SSIS controls. If this answer comes from MS then me thinks they don't know their own product very well. Just one example is Set the report to run as SQL Server Agent Job. You can trigger it from within SSIS using SP_START_JOB. I also believe ( I may be mistaken since I have not used SSRS for a few years) that SSRS has a WMI provider that allows you to create a WMI watcher that can be set to look for a file creation on any server and trigger a report when a new version of the file exists. Using standard SSIS controls you can easily create such a file, after deleting the original, thus changing the timestamp of the file. If my memory does not escape me, and their is a good chance that it does, I believe I used this very technique in 2008 at FatHead to do exactly that.

Note from the Author or Editor:
I can only agree with Arthur. However, I don't know the author(s) of the practice test questions and don't have the source files. Therefore, I don't know what did the author(s) exactly mean. O'Reilly, please contact the author(s) of the practice test questions and please explain them how the importance of correcting the questions. Dejan Sarka

Arthur Hill  Dec 11, 2013 
PDF
Page General comment on questions in Practice Test
Everywhere

PLEASE FORWARD TO WHOEVER CREATES THE QUESTIONS ON THE PRACTICE TEST. As a way of introduction, I am a CPA, MBA in Economics, and once upon a time: A MCSD, MCDBA, college instructor of Beginning, Intermediate, Advanced, and Cost Accounting. I have come to the conclusion that the people who write these questions have no real world experience in the subject matter areas touched upon, other than the MS product for which they are creating test question, or in the proper utilization of the English language. I have made previous submissions, noting the fact that the difference between nouns and verbs seem to escape them. This is the final submission I will make. After having spent a good 25% of my time while studying this book simply making sure that I was integrating the countless pages of errata into the text, I assure you I will not be purchasing any more books from this line. The following statement was made in the explanation section of a question from the practice test. "ReorderPoint represents the product reorder point from the source system. This attribute would be useful to provide grouping of product measures. Because there are no NULLS, this column can be extracted and loaded without additional transformation." REORDER POINT IS NOT A MEASURE OF A PRODUCT IN ANY WAY, SHAPE, OR FORM. "Reorder Point is the inventory level at which the product is to be reordered. It has nothing to due with any attributes of the product. In fact the reorder point of a product may change daily, or by the minute, depending on order levels, transit times, future order estimations, inventory loss levels, number of shifts running, season of the year, and a thousand other factors which are used in an MRP/ERP system. (I help design some of the first ERP systems ever written for micros) I have never seen the reorder point used as criteria, filter, or subject for a report, other than an 'items needed to be ordered' report, in my life. Having such spurious answers presented as being correct, steering those trying to learn the product in the wrong direction, makes me wonder how many such errors exist in the test bed and if you really give a damn about the quality of the product you produce or just getting it to market first.

Note from the Author or Editor:
Disclaimer: The authors of the book and the authors of the practice test questions are completely different persons. The authors of the book have no clue who the authors of the practice test questions are. As the lead author, I contacted O'Reilly and tasked them to transfers the errors and remarks for the practice test questions to the appropriate authors. However, the answer I got is that they lost the contact with the authors of the practice test questions. I don't have access to the questions source. Therefore, I am trying to do my best to suggest the corrections, to try to figure what the author meant etc. Thanks to all of the readers for their remarks, and please accept apologizes for the inconvenience. Dejan Sarka Suggested correction: find the question where the reorder point is marked as the correct answer. Replace the reorder point with some attribute that makes sense in this context. For example, use color or size.

Arthur Hill, CPA, MBA, former MCSD, MCDBA  Dec 11, 2013 
PDF
Page Test Question
Question Regtarding CDC

Please submit this to whomever writes the test questions for this book. I would really appreciate an answer to the following. I am beginning to wonder if I am wasting my time on these questions. You have a question: Which two methods are the best-performing ways to detect change in incoming rows? One of the 'Correct Answers' is 'Change Data Capture' According to the text of the book for which these questions are created: 'Change Data Capture (CDC) is a new component in SQL Server 2012 that allows the retrieval of ONLY CHANGED DATA'. The question says 'ways to detect changes in incoming rows?' The only incoming rows that CDC returns are 'CHANGED ROWS'. Why would you need to detect what rows are CHANGED when the set of rows INCOMING are by their very nature 'CHANGED'. arthurleehill@gmail.com

Note from the Author or Editor:
Disclaimer: The authors of the book and the authors of the practice test questions are completely different persons. The authors of the book have no clue who the authors of the practice test questions are. As the lead author, I contacted O'Reilly and tasked them to transfers the errors and remarks for the practice test questions to the appropriate authors. However, the answer I got is that they lost the contact with the authors of the practice test questions. I don't have access to the questions source. Therefore, I am trying to do my best to suggest the corrections, to try to figure what the author meant etc. Thanks to all of the readers for their remarks, and please accept apologizes for the inconvenience. Dejan Sarka My guess here is that the author of the question uses "changed" in the broader sense, meaning "new", "changed", or "deleted".

Arthur Hill  Dec 21, 2013 
Printed, PDF
Page i & ii
bottom of i and top of ii

In the exam subject coverage matrix '3.3 Implement control flow.' and '4.1 Troubleshoot data integration issues.' both list Chapter 13 Lessons 1, 2, and 3 but I can only find 2 lessons in the book. Is the lesson missing or is the matrix incorrect? Are the needed subjects actually being covered?

Note from the Author or Editor:
The matrix is incorrect, the chapter is correct, and all of the topics are covered. Please replace lessons for chapter 12 twice, both time replace "Lessons 1, 2, and 3" with "Lessons 1 and 2". The text mentioned appears at the bottom of page i and at the top of page ii.

Rick Bielawski  Dec 31, 2013 
Other Digital Version
1
Question Explanation

In the included testing application question 463P_5.20_02 includes numerous entries of "qds_" when those all should be replaced wtih "dqs_" For exmple one of the possible answers is "Only members of the qds_administrators role." qds_administrator should be dqs_administrator. As currently written none of the answers are correct because they call include references to roles that do not exist.

Note from the Author or Editor:
Confirmed; however, the writers cannot do much about this. Please inform the questions author about this error.

Zack Jones  Jul 17, 2013 
Printed
Page 1
1

It's shocking how many errors there are in this book. I'm trying to prepare for an exam and am spending 90% of my time debugging your samples. I will not buy another book in the series again.

Note from the Author or Editor:
I am sorry you are disappointed. Many of the examples were written with SQL 2012 CTP3 and RC0, as the book was written mostly before RTM, and re-checked after. We corrected many errors and prepared a new download. You can find the latest download at http://examples.oreilly.com/9780735666092-files/. It would be really great if you could check it. Best regards, Dejan Sarka

Danny Quinn  Nov 17, 2013 
Printed, PDF
Page 9
4th paragraph

"However, in the DimDate dimension, if you know the month, you obviously know the calendar quarter, and if you know the calendar quarter, you know the calendar year." There is a functional dependency between month and quarter. This is NOT true for quarter and year. For example, knowing it is the 2nd quarter does not identify the year, as each and every year has a 2nd quarter.

Note from the Author or Editor:
The last statement in th e4th paragraph reads: However, in the DimDate dimension, if you know the month, you obviously know the calendar quarter, and if you know the calendar quarter, you know the calendar year. It should be: However, in the DimDate dimension, if you know the month, you obviously know the calendar quarter, and if you know the calendar quarter, you know the calendar semester.

Malcolm  Jan 16, 2013  Aug 16, 2013
Printed, PDF
Page 18
3rd paragraph

On page 6, you wrote about using a DW to analyze data by country. "For example, you might have only the latest customer address, which might prevent you from calculating historical sales by country correctly." On page 18, you write about how these values are member properties, rather than attributes (as your previous comment would indicate). "A customer typically has an address, a phone number, and an email address. You do not analyze data on these columns. You do not need them for pivoting." Not a big deal, but a good opportunity to point out the risks in confusing member properties and attributes. In different DWs, they may be different types.

Note from the Author or Editor:
The reader is right, the statement on page 6 could be rephrased for clarification. This is the last statement of the third paragraph on page 6. Instead of: For example, you might have only the latest customer address, which might prevent you from calculating historical sales by country correctly. Should read: For example, you might have only the latest customer address (from which you extract customer's current country), which might prevent you from calculating historical sales by country correctly.

Malcolm  Jan 16, 2013  Aug 16, 2013
PDF
Page 20
Nederland

Half way down the page: "edimension" should be "dimension"

Note from the Author or Editor:
The first sentence after the figure, which reads "Some attributes of the DimDate edimension include the following (not in the order shown in the figure):", should read "Some attributes of the DimDate dimension include the following (not in the order shown in the figure):".

Robert  Jun 17, 2013  Aug 16, 2013
Printed, PDF
Page 26
Exercise 2 - 4th bullet

The text refers to a column in the DimSalesReason dimension table named "SalesReasonType", but the column name is actually "SalesReasonReasonType" in the AdventureWorksDW2012 database (and is correctly displayed in the screenshot on p25).

Note from the Author or Editor:
The last sentence of the fourth bullet of Exercise 2 (top of the page) should be modified from "In the DimSalesReason dimension, it seems that there is a natural hierarchy: SalesReasonType &#10141; SalesReasonName." to "In the DimSalesReason dimension, it seems that there is a natural hierarchy: SalesReasonReasonType &#10141; SalesReasonName.".

Daniel Dittenhafer  May 02, 2013  Aug 16, 2013
PDF
Page 28
5th paragraph, 2nd sentence

In the second sentence, "In addition, the Source Order Details table...", the word "Source" is capitalized and in italics. This makes it appear as if the table name is "Source Order Details". Change the word "Source" to be lower case and not italicized.

Note from the Author or Editor:
The last sentence before table 1-8, which is "In addition, the Source Order Details table has the ProductId foreign key column. The Quantity column is the measure.", should be modified. The word "Source" should no be capitalized and in italics.

truncheon411  May 01, 2013  Aug 16, 2013
PDF
Page 44
MORE INFO Shaded Area

Just wanted to point out that due to word-wrapping the link in the PDF file for the SQLCAT Whitepaper doesn't work. There's a space between "06" and "/" which results in a not-found error on the SQLCAT Web Site. If you remove the space from the URL in your web browser it works. Perhaps you could replace the super-long hyperlink with a shortened version?

Note from the Author or Editor:
Thank you for the observation. However, as the book template does not anticipate shortened URLs, we cannot do much now. A note to the publisher.

Zack Jones  Apr 08, 2013  Aug 16, 2013
Printed, PDF
Page 51
Exercise 2, Item 2

This is a minor tweak to a great exercise, in a very helpful introductory chapter. The calculation for Age Age AS CASE WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) <= 40 THEN 'Younger' WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) > 50 THEN 'Older' ELSE 'Middle Age' END does not really deal with NULL values for BirthDate. Regardless of whether there are NULL values currently in the data, the BirthDate field allows them, so the code should be prepared to deal with them. Age AS CASE WHEN BirthDate IS NULL THEN NULL WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 50 THEN 'Older' WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 40 THEN 'Middle Age' ELSE 'Younger' END

Note from the Author or Editor:
I agree with the reader. Just to make clear what needs the change: the code starts on page 50, at the bottom, bullet 2. The complete code for creating the dbo.Customers table, which spans over page 51 as well, is now: CREATE TABLE dbo.Customers ( CustomerDwKey INT NOT NULL, CustomerKey INT NOT NULL, FullName NVARCHAR(150) NULL, EmailAddress NVARCHAR(50) NULL, BirthDate DATE NULL, MaritalStatus NCHAR(1) NULL, Gender NCHAR(1) NULL, Education NVARCHAR(40) NULL, Occupation NVARCHAR(100) NULL, City NVARCHAR(30) NULL, StateProvince NVARCHAR(50) NULL, CountryRegion NVARCHAR(50) NULL, Age AS CASE WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) <= 40 THEN 'Younger' WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) > 50 THEN 'Older' ELSE 'Middle Age' END, CurrentFlag BIT NOT NULL DEFAULT 1, CONSTRAINT PK_Customers PRIMARY KEY (CustomerDwKey) ); and it should be changed to: CREATE TABLE dbo.Customers ( CustomerDwKey INT NOT NULL, CustomerKey INT NOT NULL, FullName NVARCHAR(150) NULL, EmailAddress NVARCHAR(50) NULL, BirthDate DATE NULL, MaritalStatus NCHAR(1) NULL, Gender NCHAR(1) NULL, Education NVARCHAR(40) NULL, Occupation NVARCHAR(100) NULL, City NVARCHAR(30) NULL, StateProvince NVARCHAR(50) NULL, CountryRegion NVARCHAR(50) NULL, Age AS CASE WHEN BirthDate IS NULL THEN NULL WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 50 THEN 'Older' WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 40 THEN 'Middle Age' ELSE 'Younger' END CurrentFlag BIT NOT NULL DEFAULT 1, CONSTRAINT PK_Customers PRIMARY KEY (CustomerDwKey) ); GO

Malcolm  Jan 18, 2013  Aug 16, 2013
Printed, PDF, ePub
Page 52
Exercise 3 Create a Fact Table

The text has: "In this example, you cannot use all foreign keys together as a composite primary key ... and the primary key would be duplicated. You could use ... however, in order to show how you can autonumber a column with the IDENTITY property, this exercise has you add your own integer column with this property. This will be your surrogate key. CREATE TABLE dbo.InternetSales ( InternetSalesKey INT NOT NULL IDENTITY(1,1), CustomerDwKey INT NOT NULL, ProductKey INT NOT NULL, DateKey INT NOT NULL, OrderQuantity SMALLINT NOT NULL DEFAULT 0, SalesAmount MONEY NOT NULL DEFAULT 0, UnitPrice MONEY NOT NULL DEFAULT 0, DiscountAmount FLOAT NOT NULL DEFAULT 0, CONSTRAINT PK_InternetSales PRIMARY KEY (InternetSalesKey) );" But FIGURE 2-1 does not have InternetSalesKey as a column of the InternetSale table and shows CustomerDwKey, ProductKey, and DateKey as the primary key.

Note from the Author or Editor:
I already sent the corrected figure to the publisher.

Anil Das  Apr 25, 2013 
Printed
Page 60
Last Paragraph above Shaded NOte area

The first sentence starts out with "The query returns 6343 rows..." Actually it returns 6434. This can be confirmed by running the following: Select count(*) from dbo.FactInternetSales where CustomerKey <= 12000; The result of that query is 6434. Also not that it really matters but my logical read counts are significanty different than yours: FactInternetSales logical reads - 4534 DimCustomer logical reads - 196 Worktable logical reads - 21314 What could cause such big differences?

Note from the Author or Editor:
The beginning of the first sentence of the last paragraph before the note should be modified from "The query returns 6,343 rows and performs..." to "The query returns 6,434 rows and performs...". About different numbers for logical IO: you cannot guarantee that you would always get the same execution plan. This depends on your HW, database settings, indexes and more. There is actually a note at the bottom of the page: "Note Number of Logical Reads You might get slightly different numbers for logical reads; nevertheless, you should get many more logical reads from the first query than from the second query." The important thing here is that you definitely should get less logical reads from the second query, which uses the Window functions.

Zack Jones  Apr 10, 2013  Aug 16, 2013
Printed
Page 63
2nd paragraph

View 'sys.column_store_index_stats' no longer exists

Note from the Author or Editor:
Yes, the reader is right, the view was removed in RTM. It was still there in CTP3 when I wrote the chapter. The second paragraph of the page should be changed from "There are three new catalog views you can use to gather information about columnstore indexes: &#9632;&#9632; sys.column_store_index_stats &#9632;&#9632; sys.column_store_segments &#9632;&#9632; sys.column_store_dictionaries" to "There are two new catalog views you can use to gather information about columnstore indexes: &#9632;&#9632; sys.column_store_segments &#9632;&#9632; sys.column_store_dictionaries"

Anonymous  Apr 01, 2013  Aug 16, 2013
PDF
Page 64
Quick Check frame

Under Quick Check Answers there is a little typo: 2. No, you should use age compression only for data warehousing environments. Should be page compression since I am not aware of age compression yet.

Note from the Author or Editor:
The second answer in the Quick Check section should be changed from: 2. No, you should use age compression only for data warehousing environments. to: 2. No, you should use page compression only for data warehousing environments.

Lars Utterström  Jan 10, 2013  Aug 16, 2013
Printed, PDF
Page 65, 67
tables 2-5, 2-8

2 similar misplacements: pg.65: text under Remarks for the CustomerKey column applies to the following column (FullName). pg.67: text under Remarks for the ProductKey column applies to the following column (DateKey).

Note from the Author or Editor:
Page 65, table 2-5, text "Concatenate FirstName and LastName from DimCustomer" in the Remarks column, which is currently in the CustomerKey row, should be moved to the FullName row. Page 67, table 2-8, text "OrderDateKey from FactInternetSales" in the Remarks column, which is currently in the ProductKey row, should be moved to the DateKey row.

Ed  Sep 17, 2013 
Printed
Page 67
Exercise 1 Step 4

In table 2-7 the remarks for column MonthNumberName state that it should concatenate MonthNumberOfYear and EnglishMonthName. In the INSERT statement, the MonthNumberName column is concatenating SUBSTRING(CONVET(CHAR(8), FullDateAlternateKey,112), 5,2) (not MonthNumberOfYear as it states in table 2-7) and EnglishMonthName. Should SUBSTRING(CONVET(CHAR(8), FullDateAlternateKey,112), 5,2) be replaced with Format(MonthNumberOfYear,'00')

Note from the Author or Editor:
Nice finding. Although both the code and the instructions are correct, a different technique is used in the code. Please correct in the code as stated in the error. Replace SUBSTRING(CONVET(CHAR(8), FullDateAlternateKey,112), 5,2) with FORMAT(MonthNumberOfYear,'00') As the reader, I would also prefer to correct the code than the instructions, because the newly suggested code is more SQL Server 2012 oriented. Dejan Sarka

Anonymous  Nov 07, 2013 
Printed
Page 68
Exercise 2 Steps 1, 3, and 8

The @updateusage parameter of the system stored procedure sp_spacedused is a varchar datatype not nvarchar so all references to @updateusage = N'TRUE' should be changed to @updateusage='TRUE'

Note from the Author or Editor:
I checked, and this is correct. The sys.sp_spaceused system procedure's second parameter, the @updateusage parameter, is VARCHAR(5) data type. However, it seems to me that this is an inconsistency from MS. Nevertheless, the error is really minor, so I guess it makes no sense to change it all over the chapter.

Zack Jones  Apr 11, 2013  Aug 16, 2013
Printed
Page 74
Step 5

Step 5 tells us to recreate the dbo.Internetsales table. Because it already exists the statement will fail. Before you can recreate it you must first drop it. Recommend adding a step between 4 and 5 that tells the reader to drop internetsales before trying to recreate it.

Note from the Author or Editor:
Actually, step 2 says to drop the table, so this is not an error. However, there is a real error in step 5, which reads: "5. Re-create the FactInternetSales table." This first sentence shoul be changed to "5. Re-create the InternetSales table."

Zack Jones  Apr 11, 2013  Aug 16, 2013
Printed, PDF
Page 78
lesson review, question 2, answer E

As noted on p.73, if a partitioned table also has a columnstore index, it is added to the auxiliary table only after the data is loaded and remains in place until the partitions are switched. During this period, answer E is valid. However, at all other times, the auxiliary table doesn't have a columnstore index, in order to make data loads possible. During this period, answer E does not apply. P.72 seems to define the partition switching process as starting after the data load and ending after the partition switch. Perhaps the question can be phrased as "You have inserted data into an unpartitioned table and want to switch it with a partition..." to make the situation more explicit.

Note from the Author or Editor:
Page 73, Question 2, question text should be clarified from "If you want to switch content from a nonpartitioned table to a partition of a partitioned table, what conditions must the nonpartitioned table meet?" to "You have inserted data into an unpartitioned table and want to switch the content from this table ta a partition of a partitioned table. What conditions must the nonpartitioned table meet?"

Ed  Sep 17, 2013 
PDF
Page 89
Planning a Simple Data Movement

Planning a Simple Data Movement To determine whether the and Export Wizard is the right tool for a particular data movement, ask yourself a few simple questions Maybe the word Import have fallen out and should be added as the 5th word in that sentence.

Note from the Author or Editor:
Thank you for reporting this. You are correct; the sentence should read: To determine whether the Import and Export Wizard is the right tool for a particular data movement, ask yourself a few simple questions ML

Lars Utterstrom  Jan 16, 2013  Aug 16, 2013
PDF
Page 99,100
last section

Lesson 1 Review Question 1 and Lesson 1 Review Question 2 lack the standard text, "(Choose all that apply.)". In the answer section, both questions have more than one answer specified as a solution. This is misleading to the reader. Add the standard text to both questions 1 and 2.

Note from the Author or Editor:
Add the text "(Choose all that apply.)" to the end of questions 1 and 2 of Lesson 1 Review. ML

truncheon411  May 03, 2013  Aug 16, 2013
Printed
Page 108
First Question

SSDT stands for SQL Server Management Data Tools, not SQL Server Development Tools.

Note from the Author or Editor:
Please, replace the text "SQL Server Development Tools" with "SQL Server Data Tools". The change should be applied in Chapter 3, "Creating SSIS Packages", but not in Chapter 11, "Installing SSIS and Deploying Packages", which has been addressed in another erratum (submitted on February 5th 2013 by Malcolm). ML

Carlos Cipriano  Jun 26, 2013  Aug 16, 2013
Printed
Page 124
Lesson Review Question 1

Answer to review question 1. is "A, B" Possibly should include "C" also as the SQL Task Editor is not just 'a text box into which you type or paste an SQL statement', it comes with its own query builder which can be used to both develop and test SQL statements.

Note from the Author or Editor:
"Execute SQL Task Editor" should be replaced with "Execute T-SQL Statement Task Editor" on the following pages: 1) Page 124, Question 1, Answer C; and 2) Page 128, Question 1 for Lesson 3, Answer C.

Anonymous  Apr 02, 2013  Aug 16, 2013
Printed
Page 133
First paragraph

"mail servers" appears twice in the list in the first sentence.

Note from the Author or Editor:
Please, remove the second occurrence of the phrase "mail servers" from paragraph 1 on page 133. ML

Scott Mancuso  Mar 15, 2014 
Printed
Page 150
In the "important" note

The note for bulk insert task and permissions states that "The bulk insert task requires the user who is executing the ssis package that contains this task to be a member of the sysadmin fixed server role." This should state the user needs to be a member of the "bulkadmin" server role. Sysadmin permissions are not necessary.

Note from the Author or Editor:
The text is based on the official documentation. I will have to verify this with the vendor. ML [UPDATE, Oct. 24 2013]: I am still investigating this issue. ML [UPDATE, Nov. 26 2013]: I have asked my contacts at the vendor several times, but unfortunately have not received any definite answers. I will give them a few more days, and then prepare a correction based on my own observations, which may or may not be 100% correct, it will, however, be more accurate than what is said in the vendor documentation. ML I will send the amended text via email. ML UPDATE from ML on March 18, 2014: The “Important” reader aid should be replaced with this one: IMPORTANT THE BULK INSERT TASK AND PERMISSIONS According to vendor documentation, the Bulk Insert task requires the user who is executing the SSIS package that contains this task to be a member of the sysadmin fixed server role. However, based on practical experience with SSIS 2012, it seems that the minimum set of permissions required to execute the task is far less than what is said in the documentation. The user executing an SSIS package, in which the Bulk Insert task is used, actually needs the following permissions: a) the user must be a member of the bulkadmin fixed server role; and b) must either have ALTER TABLE permissions on each destination table, or be a member of the db_ddladmin database role; and c) the user must have INSERT permissions on each destination table, or be a member of the db_datawriter database role.

Mark Wojciechowicz  Aug 29, 2013 
Printed
Page 150
Execute sql task

SQLMOBILE is listed as a data provider for this task. Technet indicates that this is the connection type for SQL Server Compact Edition, but this is not indicated elsewhere in the book (as of chapter 4). A good place might be the list of connection managers on p.135.

Note from the Author or Editor:
Please add the following sentence to the description of the "Execute SQL Task" task, as the last sentence of paragraph 1 (right after "...planning connection managers."): "The SQLMOBILE provider is used for connections to SQL Server Compact Edition instances." ML

Ed  Sep 25, 2013 
Printed, PDF
Page 180
EXAM TIP

"At run time, the data flow task builds an execution plan from the data flow, and the data flow engine executes the plan." Would this be improved with the inclusion of "definition" before the second comma? As below? "At run time, the data flow task builds an execution plan from the data flow definition, and the data flow engine executes the plan."

Note from the Author or Editor:
Agree. The existing text should be changed from: "At run time, the data flow task builds an execution plan from the data flow, and the data flow engine executes the plan." to: "At run time, the data flow task builds an execution plan from the data flow definition, and the data flow engine executes the plan."

Bodhi Densmore  Feb 01, 2013  Aug 16, 2013
Printed
Page 192
Ch.5, lesson1, exercise 2

I run an x64 system, and this exercise failed to work for me unless I did two things: 1. Run SSDT as an admin (required to create the system DSNs). 2. Set the project debugging property 'run64bitruntime' to false (ODBC connections apparently require 32bit, though this wasn't noted in the text.) The package will run using user DSNs, without having to do the above items. I also disabled the tasks set up in exercise 1. This may be worth mentioning as a useful debugging feature.

Note from the Author or Editor:
On page 193, modify please step 16 from: 16. Execute the FillStageTables.dtsx package. Observe the execution to confirm successful completion of this exercise. to: 16. Execute the FillStageTables.dtsx package. Observe the execution to confirm successful completion of this exercise. If you get an error, please check if you have created a system DSN for the ODBC connection or try to set the project debugging property "Run64BitRuntime" to false. Thank you and regards Grega

Ed  Sep 28, 2013 
Printed
Page 194
Step 1 of exercise 3

In the create table code for stg.CustomerInformation. The gender and maritalstatus columns are defined as char(1). On page 208 the dbo.Customers table has those columns as nchar(1). This results in conversion errors. Later, on page 213, in table 5-7, we are directed to set the default, if NULL, to "N/A" which doesn't fit in the single char. I'm not sure how far back this goes and how it matches the table in AdventureWorks but it is causing difficulties moving through the exercises.

Note from the Author or Editor:
Please change on page 50 in table 2-1 the data types for columns: Gender NCHAR(1) to NCHAR(5) MaritialStatus NCHAR(1) to NCHAR(5) Also on page 50 in step 2 of the exercise change the CREATE TABLE sentence to include changes: CREATE TABLE dbo.Customers ( CustomerDwKey INT NOT NULL, CustomerKey INT NOT NULL, FullName NVARCHAR(150) NULL, EmailAddress NVARCHAR(50) NULL, BirthDate DATE NULL, MaritalStatus NCHAR(5) NULL, Gender NCHAR(5) NULL, Education NVARCHAR(40) NULL, Occupation NVARCHAR(100) NULL, City NVARCHAR(30) NULL, StateProvince NVARCHAR(50) NULL, CountryRegion NVARCHAR(50) NULL, Age AS CASE WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) <= 40 THEN 'Younger' WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) > 50 THEN 'Older' ELSE 'Middle Age' END, CurrentFlag BIT NOT NULL DEFAULT 1, CONSTRAINT PK_Customers PRIMARY KEY (CustomerDwKey) ); On page 65, exercise 1, step 2, table 2-5 please change the data types: for Gender NCHAR(1) to NCHAR(5) for MaritialStatus NCHAR(1) to NCHAR(5) On page 194, Exercise 3, step 1 please change the CREATE TABLE to look like this: CREATE TABLE stg.CustomerInformation ( PersonID INT NULL, EnglishEducation NVARCHAR(30) NULL, EnglishOccupation NVARCHAR(50) NULL, BirthDate DATE NULL, Gender NCHAR(5) NULL, MaritalStatus NCHAR(5) NULL, EmailAddress NVARCHAR(50) NULL );

Anonymous  Mar 18, 2013  Aug 16, 2013
PDF
Page 194
Step 1

The CREATE TABLE stg.CustomerInformation statement in the book creates the Gender and MaritalStatus as data type CHAR(1). The CREATE TABLE stg.CustomerInformation statement in the file TK463Chapter05.sql creates the Gender and MaritalStatus as data type NCHAR(1). Please synchronize the table DDL between the book and the SQL file.

Note from the Author or Editor:
Fixed in the sql file.

truncheon411  Jun 11, 2013  Aug 16, 2013
Printed, PDF
Page 196
middle of the page (12.)

Point 12 tells me to use the suggested data types to import the data from the file. For gender and marital status, SSIS considers unicode strings of length 1. As we created the corresponding table with char(1) for those two attributes at the beginning of this exercise, the execution of this part fails.

Note from the Author or Editor:
Fixed in all scripts and projects to have the gender and marital status of type nchar(5). Please also change on page 209, Exercise 1, step 1 the new CREATE TABLE for dbo.Customers: -- Customers dimension with a PK CREATE TABLE dbo.Customers ( CustomerDwKey INT NOT NULL, CustomerKey INT NOT NULL, FullName NVARCHAR(150) NULL, EmailAddress NVARCHAR(50) NULL, BirthDate DATE NULL, MaritalStatus NCHAR(5) NULL, Gender NCHAR(5) NULL, Education NVARCHAR(40) NULL, Occupation NVARCHAR(100) NULL, City NVARCHAR(30) NULL, StateProvince NVARCHAR(50) NULL, CountryRegion NVARCHAR(50) NULL, Age AS CASE WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) <= 40 THEN 'Younger' WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) > 50 THEN 'Older' ELSE 'Middle Age' END, CurrentFlag BIT NOT NULL DEFAULT 1, CONSTRAINT PK_Customers PRIMARY KEY (CustomerDwKey) ); GO GJ

Anonymous  Jan 29, 2013  Aug 16, 2013
PDF
Page 196
Step 12

<<Problem with BirthDate>> In Flat File Advanced, Suggest type has selected data type DT_I2 for BirthDate. The values for BirthDate in CustomerInformation.txt contains integer type in the format 1987802. OLE DB Destination successfully imported the BirthDate values to table stg.customerinformation, resulting in invalid date in BirthDate column which has a data type of DATE. The data flow needs to address the formatting of Birthdate before importing to OLE DB Destination. Also, some BirthDate values in CustomerInformation.txt contains invalid date values such as 1958003, see person ID 387. If you search for 00 you will see many more. I would suggest the data flow as below: Change the data type for BirthDate to DT_WSTR in Connection Manager CustomerInformation. Flat File source > Derived Column (to extract month from BirthDate) > Derived Column (to check month is > 1, if not substitue with a valid value) > Derived Column (concatenate year-month-day) > OLE DB Dest. Cheers, Hannah

Note from the Author or Editor:
Flat file suggest type was probably done only on a sample, so it has selected DT_I2. There are different possibilities, one is as you have suggested, the other one is to set it up as DT_I4 and the date data should be all in 8 characters as unified date recognition for SQL Server. Regarding the data in the file, there was a bug when preparing the data and now in the new version I will fix the file. Thank you GJ

Hannah  Jun 24, 2013  Aug 16, 2013
Printed, PDF
Page 201
Multicast

Small typo - "Generates one or more identical outputs" not "Generates one or mode identical outputs"

Note from the Author or Editor:
The text "Generates one or mode identical outputs" should be changed to "Generates one or more identical outputs". Thank you for submitting this typo. Grega

Anonymous  Mar 04, 2013  Aug 16, 2013
Printed, PDF
Page 210
Exercise 1, Item 5

Small typo - the database to connect to is TK463DW, not TK463

Note from the Author or Editor:
Thank you for noticing this. The item 5 in Exercise 1 should be changed from : 5. Drag another OLE DB source adapter onto the workspace and rename it stgCustomer. In the OLE DB Source Editor, set the OLE DB connection manager to TK463 and select the stg.Customer table. To: 5. Drag another OLE DB source adapter onto the workspace and rename it stgCustomer. In the OLE DB Source Editor, set the OLE DB connection manager to TK463DW and select the stg.Customer table.

Malcolm  Jan 24, 2013  Aug 16, 2013
PDF
Page 212
Step 6

The text of Exercise 2 Step 6 is missing instruction on how to answer the prompt of the Input Output Selection dialog box. Add this text as the last sentence of Step 6: "When prompted with the Input Output Selection dialog box, choose Lookup No Match Output from the Input drop-down list, and then click OK."

Note from the Author or Editor:
The Step 6 on page 212, should have an additional sentence at the end: "When prompted with the Input Output Selection dialog box, choose Lookup No Match Output from the Input drop-down list, and then click OK." BR Grega

truncheon411  Jun 01, 2013  Aug 16, 2013
Printed
Page 214
Step 14

In step 14 is missing instruction on how to answer the prompt Input Output Selection. Should have an additional sentence at the end: "When prompted with the Input Output Selection dialog box, choose Lookup Match Output from the Input drop-down list, and then click OK."

Note from the Author or Editor:
Please change the text in step 14 from: Drag a Derived Column transformation from the SSIS Toolbox and connect the output arrow from the Check Customer Exists Lookup transformation to the new Derived Column transformation. Rename the Derived Column transformation Calc Columns. To: Drag a Derived Column transformation from the SSIS Toolbox and connect the output arrow from the Check Customer Exists Lookup transformation to the new Derived Column transformation. When prompted with the Input Output Selection dialog box, choose Lookup Match Output from the Input drop-down list, and then click OK. Rename the Derived Column transformation Calc Columns. Thank you Grega

Carlos Cipriano  Jul 09, 2013  Aug 16, 2013
PDF
Page 226
2nd paragraph

The last sentence in the paragraph is missing its closing parenthesis. However, it may be better so simply drop this sentence, since Chapter 2 provides no explanation of the T-SQL MERGE statement, other than a passing mention of its existence. This: (If you are not familiar with the T-SQL MERGE statement, see Chapter 2. change to this: (If you are not familiar with the T-SQL MERGE statement, see Chapter 2.)

Note from the Author or Editor:
The last sentence of the 2nd paragraph, the sentence "(If you are not familiar with the T-SQL MERGE statement, see Chapter 2.", should be deleted.

truncheon411  May 06, 2013  Aug 16, 2013
PDF
Page 227
Exercise 1, Step 2 (for Lesson 3)

Since Lesson 2, Exercise 1, step 14 on page 211 did not explicitly name "sort 2" and "sort 3", the delete directions in step 2 regarding "Sort 3" would benefit from more specific instructions. First, although my "Sort 3" followed my "Sales Territory", I believe you were actually referring to the sort transform that followed the first merge join for bringing stgPerson and stgCustomer together. Of course, adding more exact instructions in Exercise 1, step 14 is not a bad idea either. Nonetheless, I recommend following change for Lesson 3, Exercise 1, step 2: "Select the stgCustomer, stgPerson, Sort, Sort 1, Merge Join and Sort 3 components"... to the following: "Select the stgCustomer, stgPersion, Sort (sort step after stgPerson), Sort 1 (sort step after stgCustomer), Merge Join and Sort 3 (sort step after Merge Join) components"...

Note from the Author or Editor:
Please change the step 2 of the exercise 1 from: Click the Data Flow tab at the top of the SSIS Designer. Select the stgCustomer, stgPerson, Sort, Sort 1, Merge Join, and Sort 3 components, and press Delete on the keyboard. You will now replace the existing logic of sorting and merging data by doing this on the database layer. to: Click the Data Flow tab at the top of the SSIS Designer. Select the stgCustomer, stgPerson, Sort (sort step after stgPerson), Sort 1 (sort step after stgCustomer), Merge Join and Sort 3 (sort step after Merge Join) components, and press Delete on the keyboard. You will now replace the existing logic of sorting and merging data by doing this on the database layer. Thank you Grega

Paul Neumann  Jul 16, 2013  Aug 16, 2013
PDF
Page 230
Table 5-10 (end of page)

Under the "Destination column" heading: "EnglishEducation" should be "Education" "EnglishOccupation" should be "Occupation" The CREATE TABLE statement at the top of page 230 shows that these two destination columns do not have "English" prepended to them.

Note from the Author or Editor:
On page 230 in table 5-10 please change the EnglishEducation to Education and EnglishOccupation to Occupation. Grega

Paul Neumann  Jul 17, 2013  Aug 16, 2013
Printed, PDF
Page 232
Lesson Review 1

Which data flow transformation would you use if had to combine data from two different database tables that exist on two different servers? should read Which data flow transformation would you use if YOU had to JOIN data from two different database tables that exist on two different servers? The "incorrect" answers are the ones that combine data through methods other than joins, so the question is a bit imprecise.

Note from the Author or Editor:
Agree. The question "Which data flow transformation would you use if had to combine data from two different database tables that exist on two different servers?" should be changed to "Which data flow transformation would you use if you had to join data from two different database tables that exist on two different servers?" GJ

Malcolm  Jan 25, 2013  Aug 16, 2013
PDF
Page 232
Question 2

Question 2 asks "How many Data Source adapters do you need?". One only NEEDS to use one adapter...one COULD use two or three if desired. I suggest rewording the question to make it clearer.

Note from the Author or Editor:
Second sentence for question 2 on page 232, should be changed from "How many Data Source adapters do you need? (Choose all that apply.)" to "How many Data Source adapters could you use? (Choose all that apply.)"

truncheon411  May 06, 2013  Aug 16, 2013
Printed
Page 235
Lesson 2 question 3 Correct Answers

Question 3 answer is stated as "Correct Answers: B and D", but should be "A and D" as shown by the detailed analysis of choices A through D.

Note from the Author or Editor:
Thank you for noticing this typo. Question 3 answer should be : "Correct Answers A and D". Grega Jerkic

Curt Coker  Jan 16, 2013  Aug 16, 2013
PDF
Page 236
Last sentence on page

It appears two words are swapped during printing: Change from: "... from existing the dimension ..." to: "... from the existing dimension ..."

Note from the Author or Editor:
Please change the sentence : Then you would need to unpivot the columns for months to get a row for each month, and use the Lookup task to get appropriate surrogate keys from existing the dimension, and use the ODBC Destination adapter to write the data to the new fact table. to: Then you would need to unpivot the columns for months to get a row for each month, and use the Lookup task to get appropriate surrogate keys from the existing dimension, and use the ODBC Destination adapter to write the data to the new fact table.

Paul Neumann  Jul 17, 2013  Aug 16, 2013
PDF
Page 262
Exercise 1 Step 6

The phrase "between 1 for Monday and 7 for Sunday" is only correct if your system is set up with Monday as the first day of the week. In my case, the expression returns 4 for Wednesday, due to my settings. Suggest you change phrase to "between 1 for Monday and 7 for Sunday if your system is set for Monday as the first day of the week or between 1 for Sunday and 7 for Saturday if your system has Sunday as the first day of the week. The first day of the week is set using the T-SQL setting SET DATEFIRST. By the way, the default is Sunday as the first day of the week in SQL 2012.

Note from the Author or Editor:
On page 262 in Exercise 1, Step 6, the text: "(between 1 for Monday and 7 for Sunday)" should be replaced with "(between 1 for Monday and 7 for Sunday in most languages)" On page 263 in Exercise 2, after Step 6, the following note should be added: " NOTE LANGUAGE SETTINGS AND DATE/TIME FORMATS Depending on your environment, either Monday or Sunday is used as the first day of the week; by default, this is determined by the operating system regional settings. If Monday is used as the first day of the week (for instance, if the language used in your environment is British English) then Saturday and Sunday are represented by numbers 6 or 7, respectively. On the other hand, if Sunday is used as the first day of the week (for instance, if the language used in your environment is U.S. English) then Saturday and Sunday are represented by numbers 7 and 1, respectively. Please, make the appropriate adjustments to the expressions shown in Listings 6-2 and 6-3 to reflect your actual environment settings. You can find more information about SQL Server languages by inspecting the sys.syslanguages catalog view. " ML

Arthur Hill  Nov 13, 2013 
Printed
Page 263
Multiple Locations

The "DefaultBufferMaxRow" property is actually "DefaultBufferMaxRows" (Note the added "S"). The following locations need to be corrected: page 263 - Step 3 and page 263 - Text next to LIsting 6-2

Note from the Author or Editor:
Both occurrences of the expression "DefaultBufferMaxRow" on page 263 must be corrected to "DefaultBufferMaxRows". ML

Zack Jones  Jun 05, 2013  Aug 16, 2013
PDF
Page 269
Middle

The name of the parent package variable must match the name of the corresponding variable in the parent package. should be The name of the parent package variable must match the name of the corresponding variable in the child package.

Note from the Author or Editor:
The last sentence in paragraph 6 (under Package Configurations) should read: "The name of the parent package variable must match the name of the corresponding variable in the child package." ML

ScottVK  Jan 29, 2013  Aug 16, 2013
PDF
Page 272
Exercise 2 Step 5

Exercise 2 Step 5 reads: "5. In the Variables pane, create a new, project-scoped integer (Int32) variable named lastRecordCount." The package solution provided from the TK70463_CodeLabSolutions.zip file has the lastRecordCount variable created as a Char.

Note from the Author or Editor:
Thank you for noticing it. I will change the lab files. BR Grega

truncheon411  Jun 07, 2013  Aug 16, 2013
Printed
Page 272
Exercise 2, Step 5.

"project-scoped integer" should read "package-scoped integer", I did not see project scope as an option on p. 248.

Note from the Author or Editor:
On Page 272, in Step 5, the text "project-scoped integer" must be replaced with "package-scoped integer". ML

John Eiford  Dec 04, 2013 
Printed, PDF
Page 274
ex3. step3

Step4 needs an extra first step: Set "Evaluation operation" to "Expression". Click the ellipsis (...)

Note from the Author or Editor:
The text for Step 4 needs to be replaced with the following paragraph: Change the Evaluation operation constraint option from “Constraint” to “Expression and Constraint”, leave “Success” as the evaluation value, then click the ellipsis (…) at the right of the Expression text box to open the Expression Builder dialog box, and build the expression shown in Listing 6-5.

Mark Pulles  Apr 26, 2013  Aug 16, 2013
Printed
Page 275
Exercise 3, Step 8.

FillStageTables, Customer Information flat file import fails because BirthDate type is set incorrectly. If I go to Advanced Editor, Input and Output Properties and change the type to DT_Date, the task is successful. I saw the errata on Page 196, Step 12, but that says DT_I4 should work, and that's what the Starter and Solution of Ch. 6, Lesson 3 are set at as well as several previous lessons. DT_I4 doesn't work on my system. thanks, John

Note from the Author or Editor:
I was unable to reproduce the problem, even though there actually is a mismatch between the data types in the source file and the data types in the destination table. ML [UPDATE, March 17th 2014]: After a conversation with Grega Jerki&#269;, the author of Chapter 5, I have realized that the changes made in that Chapter may have been the cause of the problem in Chapter 6. I have now been able to reproduce it, and to correct it appropriately. I will send the new sample files for Lesson 3 of Chapter 6 via email. ML

John Eiford  Dec 04, 2013 
PDF
Page 276
Top of page - Question 1

Question 1 has multiple answers. Please append the text "(Choose all that apply.)" to the question.

Note from the Author or Editor:
Add the text "(Choose all that apply.)" to the end of question 1. ML

Paul Neumann  Jul 21, 2013  Aug 16, 2013
Printed
Page 295
Ch.7, lesson 1, step 17

The last data flow transform (ole db destination / insert into DimCustomer) created by the SCD wizard fails at the last transform ('next value for function cannot be used' error), unless the data access mode is set to table/view - fast load. It's set to table/view when the transform is created.

Note from the Author or Editor:
Hi, it seems that the SCD tasks fails with the sequence generator, because it uses by default the ROWCOUNT option. Thank you for pointing this out and I will add a remark to the last step. Please change the the step 17 on page 295 from: 17. Observe the created transformations and execute the package. to: 17. Observe the created transformations and execute the package. If you get an error, please change the data access mode on the destination adapter to "Table/View - fast load". There is an issue, because we are using a sequencer to populate the surrogate keys and the SCD wizard sets the destination by default to an access mode that does not allow a trigger with a sequencer on the destination table. Regards Grega

Ed  Oct 15, 2013 
PDF
Page 296
Ch7, L1, Ex1, Step 21

When running this step, the following error is encountered: "Error: 0xC020803C at Dim Customer, Slowly Changing Dimension [239]: If the FailOnFixedAttributeChange property is set to TRUE, the transformation will fail when a fixed attribute change is detected. To send rows to the Fixed Attribute output, set the FailOnFixedAttributeChange property to FALSE." I have used the starter code from the Code Lab Solutions collection, ensured that the various Gender and MaritalStatus columns have the NCHAR(5) data type (covered under the ‘Confirmed Errata’) and set the data access mode for the 'Insert Destination' transformation (generated by the SCD transformation) to 'Table or view - fast load' I believe the issue lies with the Gender column (which is defined as a fixed attribute for the SCD) and the 'Set default values' transformation. This transformation creates a derived column based on Gender and returns "N/A" when a NULL value is encountered. Given the Gender column in dbo.Customers is of type NCHAR(5), the SCD transformation will be evaluating "N/A" vs "N/A " - hence the detection of a fixed attribute change. Changing the derived column expression for Gender within the 'Set default values' transformation to: 'ISNULL(Gender) ? "N/A " : Gender' allows the data flow to complete successfully

Note from the Author or Editor:
Thank you and you are correct. There should be a NVARCHAR(5) data type and not NCHAR(5), so that you do not need to modify the SSIS. I will upload the new exercises. Thank you and regards Grega

Anonymous  Nov 01, 2013 
PDF
Page 306
4th sentence from page bottom

"If three UPDATE statements ware ..." should be: "If three UPDATE statements were ..."

Note from the Author or Editor:
Please change the sentence on page 306 under the All element of the list from: If three UPDATE statements ware applied to the same row, you will get back three records. to If three UPDATE statements were applied to the same row, you will get back three records. GJ

Paul Neumann  Aug 08, 2013  Aug 16, 2013
PDF
Page 310
Step 9

"... to the TK463 database." should be: "... to the TK463DW database." This is assertion is supported by Figure 7-10.

Note from the Author or Editor:
On page 310 in step 9 please change the sentence from: In the Configure ADO.NET Connection Manager dialog box, select New and create the connection to the TK463 database. to: In the Configure ADO.NET Connection Manager dialog box, select New and create the connection to the TK463DW database. GJ

Paul Neumann  Aug 08, 2013  Aug 16, 2013
PDF
Page 312
Step 3 in Exercise 2

"... to the TK463 database." should be: "... to the TK463DW database." Assertion is support by text inside parenthesis in step 11.

Note from the Author or Editor:
On page 312, step 3 of the exercise 2, please change the sentence from: Add a new ADO.NET connection to the TK463 database. to: Add a new ADO.NET connection to the TK463DW database. GJ

Paul Neumann  Aug 08, 2013  Aug 16, 2013
PDF
Page 312
Step 10 in Exercise 2

Without truncating the update and delete temp tables at some point before the CDC Splitter task, the two tables continue to grow and cause the "Update data" and "Delete data" Execute SQL tasks to be non-deterministic. Hence the following change for Step 10 (of course, step numbering should be adjusted accordingly): 10-1. Drag an Execute SQL task from the SSIS Toolbox onto the control flow workspace. Rename the task "Truncate tables" and double-click the new task to open the editor. 10-2. Change the Connection property to use the TK463DW connection. 10-3. In the SQL Statement property of the Execute SQL Task Editor dialog box, type the following code. TRUNCATE TABLE stg.tmpUpdateSalesOrderHeader; TRUNCATE TABLE stg.tmpDeleteSalesOrderHeader; 10-4. Add a data flow task and connect it to the "Truncate tables" Execute SQL task. Rename the data flow task "Inc Load SalesHeader".

Note from the Author or Editor:
Please add new steps in the exercise 2 on page 312 after step 33 and 34 (so that the step 34 becomes step 37): 34. Notice that there is a missing step to delete the data in stg.tmpDeleteSalesOrderHeader and stg.tmpUpdateSalesOrderHeader tables since each time you will run the package, additional data will be inserted in to these two tables. Drag an Execute SQL task from the SSIS Toolbox onto the control flow area. Rename the task "Truncate tables" and double-click the new task to open the editor. 35. Change the Connection property to use the TK463DW connection. In the SQL Statement property of the Execute SQL Task Editor dialog box, type the following code: TRUNCATE TABLE stg.tmpUpdateSalesOrderHeader; TRUNCATE TABLE stg.tmpDeleteSalesOrderHeader; 36. Set the Truncate tables Execute SQL task as the first task in the control flow by connecting it with the CDC Start CDC Control Task. 37. Write different INSERT, UPDATE, and DELETE statements against the stg.CDCSalesOrderHeader table and test your package. GJ

Paul Neumann  Aug 09, 2013 
Printed
Page 314
step 21

" ... select DeleteOutput ... - this happens automatically as the 2 other options are already selected.

Note from the Author or Editor:
Please correct the step 21 on page 314 from: Inside the IncLoadStgSalesHeader package, add another OLE DB destination adapter. Connect the CDC Splitter transformation with the new OLE DB destination adapter and, in the Input Output Selection dialog box, select DeleteOutput in the Output drop-down list. TO: Inside the IncLoadStgSalesHeader package, add another OLE DB destination adapter. Connect the CDC Splitter transformation with the new OLE DB destination adapter.

Torben Rønnow  Dec 05, 2013 
PDF
Page 317
Paragraph: Using Error Flows

In 3rd sentence: "In addition to the data paths, however,, there are also error paths." The sentence feels a little bit truncated to me or just to commas that could be de-duplicated.

Note from the Author or Editor:
Thanks for noticing this. The 3rd sentence should be changed from : "In addition to the data paths, however,, there are also error paths." to "In addition to the data paths, however, there are also error paths."

Lars Utterstrom  Feb 22, 2013  Aug 16, 2013
PDF
Page 320
Step 11

The first line of Step 11 reads: "Open the OLE DB destination adapter, and under OLD DB Connection Manager," Should read: "Open the OLE DB destination adapter, and under OLE DB Connection Manager,"

Note from the Author or Editor:
This is a typo the OLD DB Connection Manager should be changed to "OLE DB Connection Manager". lpG

truncheon411  Jun 07, 2013  Aug 16, 2013
Printed
Page 321
Step 19

FWIW when I ran the package 15 rows, not 5, were redirected to the ErrorRows flatfile.

Note from the Author or Editor:
Please change the text in step 19 on page 321 from: Execute the package. Notice that five rows were transferred through the error path, and the package ran successfully. to : Execute the package. Notice that fifteen rows were transferred through the error path, and the package ran successfully. GJ

Zack Jones  Jun 08, 2013  Aug 16, 2013
PDF
Page 321
Step 18 (top of page)

"... select the Errors For Flat File connection manager ..." should be: "... select the ErrorRows for Flat File connection manager ..."

Note from the Author or Editor:
Please change the sentence on page 321 in step 18 from: In the Flat File Destination Editor, select the Errors For Flat File connection manager and click the Mappings tab to observe how columns will be mapped. to: In the Flat File Destination Editor, select the ErrorRows For Flat File connection manager and click the Mappings tab to observe how columns will be mapped. GJ

Paul Neumann  Aug 12, 2013  Aug 16, 2013
PDF
Page 324
Answer to Lesson 3 Question 1

The answer to Lesson 3 Question 1 Choice A is "Correct: All source adapters have an error flow." This contradicts the answer to Question 3 Choice B, which reads: "incorrect: Raw File source does not have an error flow."

Note from the Author or Editor:
The answer to Lesson 3 Question 1 Choice A is "Correct: All source adapters have an error flow." It should be "Correct: The OLE DB source adapter has an error flow." GJ

truncheon411  Jun 06, 2013  Aug 16, 2013
Printed, PDF
Page 331
Chaos

Chaos is less restrictive than ReadUncommitted, so you may wish to sort them on the page accordingly. Your description (and the MS one also) doesn't help clarify what this unusual isolation level is. This is what I came to: Like ReadUncommitted, Chaos can view uncommitted changes, but does not hold update locks until the end of transaction. Furthermore, it is not supported on the SQL Server platform, is not an ANSI standard Isolation level, and cannot be rolled back. The name is descriptive, and chaos is usually not a design goal.

Note from the Author or Editor:
The existing text on page 331 : "Chaos Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten." should be changed to "Chaos Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten. Furthermore, it is not supported on the SQL Server platform, is not an ANSI standard Isolation level, and cannot be rolled back. "

Malcolm  Jan 31, 2013  Aug 16, 2013
Printed, PDF
Page 331
Snapshot

"The Snapshot value of the IsolationLevel property is incompatible with package transactions." See http://msdn.microsoft.com/en-us/library/ms137749.aspx This information affects how Snapshot can be used in SSIS.

Note from the Author or Editor:
The existing text "Snapshot The data read within a transaction will not reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read." Should be changed to: "Snapshot The data read within a transaction will not reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read. The Snapshot value of the IsolationLevel property is incompatible with package transactions. Therefore, you cannot use the IsolationLevel property to set the isolation level of package transactions to Shapshot. Instead, use an SQL query to set package transactions to Snapshot."

Malcolm  Jan 31, 2013  Aug 16, 2013
Printed, PDF
Page 332
Note: Setting Isolation Levels of a Transaction in SSIS

This note should probably be expanded to include both criteria. The IsolationLevel property in SSIS objects applies only when you: 1. Set the TransactionOption property to Required 2. The parent container has not already started a transaction

Note from the Author or Editor:
The existing note: "Note Setting Isolation Levels of a Transaction in SSIS The IsolationLevel property in SSIS objects applies only when you explicitly open a transaction inside SSIS by setting the TransactionOption property to Required." Should be modified: " Note Setting Is olation Levels of a Transaction in SSIS The IsolationLevel property in SSIS objects applies only when you explicitly open a transaction inside SSIS by setting the TransactionOption property to Required and the parent container has not already started a transaction."

Malcolm  Jan 31, 2013  Aug 16, 2013
Printed
Page 332
Quick Check Answer

I know it's nit-picky but the last two words in the answer are "Not Supported" with a space between them. The correct property is "NotSupported" without the space.

Note from the Author or Editor:
On page 332 in the quick check box please correct the sentence from: You should remove the legacy task from the sequence container or set the TransactionOption property to Not Supported. to: You should remove the legacy task from the sequence container or set the TransactionOption property to NotSupported. Thank you Grega

Zack Jones  Jun 08, 2013  Aug 16, 2013
Printed
Page 335
Step 21

I'd recommend inserting a step or note between steps 21 and 22 instructing users on how to verify that MSDTC is running. For those that don't know you can check it by doing the following (Windows 7): 1 - Click windows button to show start menu 2 - In the search window type "services", once found hit enter to start it. 3 - scroll down to Distributed Transaction Coordinator and if it's not already running click on it and then click the start option. If it's already running then you're good to go. Once you've verified that it's running proceed with steps 22 and 23.

Note from the Author or Editor:
Please change the text in step 21 from: Save the package by clicking the Save button on the toolbar. To: Save the package by clicking the Save button on the toolbar. Check if the MSDTS is running by looking at windows services if Distributed Transaction Coordinator service is running.

Zack Jones  Jun 08, 2013  Aug 16, 2013
Printed
Page 340
Exercise

The wording at the beginning of the exercise is ambiguous in terms of which project/package to use. i.e. "In this exercise, you first enable checkpoints on the sequence container in the previously created package." If you use your own package created from Lesson 1 previously, this excercise will not work correctly due to the previously set 'TransactionOption = Required' setting on the sequence container. You must either change this option back to 'Supported' or specifically use the project/package in the Lesson 2 folder. Perhaps Step 1 could be amended to specifically state that the Lesson 2 Project should be used (and not the more than likely already open Lesson 1 package!!!)

Note from the Author or Editor:
I agree. Oreilly please change the step 1 of exercise 1 on page 340 from: 1. If necessary, start SQL Server Data Tools, open the TK 463 Chapter 8 project, and open the package. to: 1. Start SQL Server Data Tools, open the TK 463 Chapter 8 project under C:\TK463\Chapter08\Lesson2\Starter\, and open the TestCheckpoints.dtsx package. Best regards Grega

Gordon Aird  Nov 14, 2013 
Printed
Page 340
step 9

You are instructed to delete the file checkpoint.xml - the file does not exist because it was deleted automatically in step 6 - which executes successfully.

Note from the Author or Editor:
FROM: In Windows Explorer, delete the checkpoint.xml file under C:\TK463\Chapter08 \Lesson2\Starter\. TO: If you did not finish the previous exercise, please check if the checkpoint.xml file exists and delete it (in Windows Explorer, delete the checkpoint.xml file under C:\TK463\Chapter08\Lesson2\Starter\ ). GJ

Torben Rønnow  Dec 06, 2013 
PDF
Page 341
Step 13 (a friendly recommendation)

Just a interesting recommendation to add a Step 13: 13. Rerun the package again to notice that all the task were run. This confirms the checkpoint.xml file was automatically deleted by the package continuing to completion in the previous step 12.

Note from the Author or Editor:
Modify please the step 12 of exercise on page 341 from: 12. Click OK to close the SQL Task Editor, and rerun the package. Notice that only the last task was run, because you are using checkpoints. to: 12. Click OK to close the SQL Task Editor, and rerun the package. Notice that only the last task was run, because you are using checkpoints. Check if the checkpoint.xml file was automatically deleted and rerun the package.

Paul Neumann  Aug 14, 2013 
PDF
Page 345
Step 14

Step 14 instructions are incorrect. This: "Set the OLE DB connection manager to TK463DW and, under Data Access Mode, select the stg.Customertable." Should be this: "Select TK463DW in the OLE DB Connection Manager list. From the Data Access Mode drop-down list, select Table Or View, and select the stg.Customertable table in the Name Of The Table Or The View drop-down list."

Note from the Author or Editor:
Please change the text of step 14 from: "Set the OLE DB connection manager to TK463DW and, under Data Access Mode, select the stg.Customer table." to: Select TK463DW in the OLE DB Connection Manager list. From the Data Access Mode drop-down list, select Table Or View, and select the stg.Customer table in the Name Of The Table Or The View drop-down list.

truncheon411  Jun 11, 2013  Aug 16, 2013
PDF
Page 345
Step 12

The original is: 12. Drag a data flow task from the SSIS Toolbox onto the Sequence Container. It is better to change it to: 12. Drag a data flow task from the SSIS Toolbox onto the Event Handler design area.

Note from the Author or Editor:
Please change the text of step 12 from exercise Use Event Handlers from 12. Drag a data flow task from the SSIS Toolbox onto the Sequence Container. to: 12. Drag a data flow task from the SSIS Toolbox onto the Event Handler design area. Thank you Grega

CKX  Jul 13, 2013  Aug 16, 2013
Printed
Page 346
ch.8 lesson 3 , exer. step 17

This step verifies the column mappings for an OLE DB destination adapter. It's in the middle of a set of instructions for configuring a flat file destination adapter, whose mappings are verified in step 20. Are the mappings applicable to some other adapter, or is this step superfluous?

Note from the Author or Editor:
Please remove step 17, on page 346: 17. On the Mapping tab of the OLE DB Destination Editor, check to make sure that all destination columns are mapped to input columns. Click OK. Regards Grega

Ed  Oct 29, 2013 
PDF
Page 367
Question 3 Answer D

Please retract my earlier report of this error. The error is actually in the Lesson Review section, not the Answers section. 'The Lookup task cache mode property' should read 'The Lookup transformation SqlCommand property'

Note from the Author or Editor:
Agree. The question 3, answer D should be changed from: "The Lookup task cache mode property" to "The Lookup transformation SqlCommand property". GJ

ScottVK  Feb 03, 2013  Aug 16, 2013
Printed
Page 376
Step 1 of Exercise

Please include the step to open the solution file in Chapter 9 lesson 2. If they try to use the one from the previous lesson, they will encounter errors since they just changed a connection manager to be converted to project connection and created parameters, both are forbidden for package configurations. The new solution for this lesson is different. (on page 366 we were told to not exit SSMS or SSDT if we intended to continue immediately. This implied the project would be reused too.)

Note from the Author or Editor:
On page 376, exercise, step 1 please change text from: If necessary, start SQL Server Data Tools, open the TK 463 Chapter 9 project, and open the FillStageTables_1.dtsx package. to: Start SQL Server Data Tools, open the TK 463 Chapter 9 project, and open the FillStageTables_1.dtsx package. GJ

Hillary  Mar 19, 2013  Aug 16, 2013
Printed
Page 392
Step 14 and 15

Step 14 and 15 refers to FillStageTables.dtsx. It should be DimCustomer.dtsx as shown in Figure 10-4

Note from the Author or Editor:
The discrepancy between text and figure is in this particular case of no real importance. An appropriate replacement image file for Figure 10-4 has been sent to the publisher. ML

Torsten Seither  Apr 28, 2013  Aug 16, 2013
Printed
Page 446
ch.11, lesson 2, exer.2, step 2

"execute only the part for of the script..." should be "execute only the part of the script..."

Note from the Author or Editor:
On Page 446 in Exercise 2, Step 2, the text "... execute only the part for of the script for the Lesson 2 practice." should be replaced with "... execute only the part of the script for the Lesson 2 practice." ML

Ed  Nov 19, 2013 
Printed
Page 449
Step 4

After clicking Next in step 4, I received the following warnings: Warning failed to decrypt an encrypted XML node...etc. and Warning failed to decrypt sensitive data in project with a user key...etc. The project deployed, but it would be helpful to include an explanation of these warnings and how they can be avoided.

Note from the Author or Editor:
The following note should be added after Step 4 of Exercise 4 on page 449: " NOTE&#8195;SENSITIVE INFORMATION STORED IN SSIS PROJECTS When sensitive information is stored inside an SSIS project, it is encrypted by default using a user key based on the credentials of the user, who created the project. As a result, other users might not be able to decrypt this information when viewing or editing the project or when they create a project deployment file; this way any sensitive data will remain protected. BIDS and other tools of the SSIS toolset might issue warnings about not being able to encrypt or decrypt sensitive data even if the project definitions do not actually contain any encrypted data, when used on projects created by another user. During the creation of the project deployment file, or when performing other exercises in this training kit, you might receive such warnings; however, because no encrypted data is actually present in the project definitions, you can safely ignore them. " ML

Anonymous  Aug 26, 2013 
Other Digital Version
463P_4.14_03
In exam

Question 463P_4.14_03 Question is: Which series of actions must you take to enable remote package execution on this server? The answer is: "Use Windows Service Manager to set the startup type of the SQL Server Agent service to Automatic and start the service." I choose "Use SQL Server Configuration Manager to enable SQL Server Agent." because I have done just this task multiple times. The Explanation states: "SQL Server Agent is installed along with the database engine. However, its startup type is set to Disabled by default. You should change this setting by using Windows Service Manager. You can then start the service from Windows Service Manager or from SQL Server Management Studio (SSMS)." It also states, incorrectly that "SQL Server Configuration Manager cannot be used to enable remote package execution or to enable SQL Server Agent." This is wrong, you CAN change the Service to start Automatically via the SQL Server Configuration Manage. And Since SQL Server Agent is the way to Execute Packages Remotely and can be enabled and alter to start up automatically via Window Service Manager and SQL Server Configuration Manager there are two correct answers

Note from the Author or Editor:
A note to O'Reilly: as the exam questions were not written by the authors of the book, we cannot correct this error. Please check with the exam questions authors. Best regards, Dejan Sarka

Anonymous  Sep 23, 2013 
Printed, PDF
Page 469
2nd paragraph

A couple of minor wording items, for your next printing. You refer to the "Object Browser" in SSMS. The SQL Query Analyzer had an Object Browser, and SSDT has one too, but SSMS has always used "Object Explorer" for some reason. Also, you describe SSDT variously as SQL Server Development Tools, and SQL Server Data Tools.

Note from the Author or Editor:
The reference "SQL Server Development Tools" is used three times throughout the book; all of these occurrences should be replaced with "SQL Server Data Tools". "Object Browser" (used 8 times) and "Object Explorer" (used >10 times) references must be verified against each particular context, where one or the other development tool is referenced. ML

Malcolm  Feb 06, 2013  Aug 16, 2013
Printed
Page 474
Exercise 3

Not necessarily a mistake but you may want to point out to the readers that if they have more than one instance of SQL Server installed on their computer that they will need to specify the full path for the DTExec.exe file. I had to edit the .cmd file to change the path to "c:\program files\Microsoft SQL Server\110\DTS\Binn\DtExec.exe" (with the quotes) Once I did that the package ran fine.

Note from the Author or Editor:
A corrected version of the CMD file has been sent to the publisher. ML

Zack Jones  Apr 30, 2013  Aug 16, 2013
Printed
Page 475
Step 4

"PowerShell_Example.ps2" should be "PowerShell_Example.ps1" Also it may be worth mentioning that some some computers have scripts disabled for powershell. I eventually got it to run by launching the PowerShell ISE and then executing the following commands > get-executionpolicy -CurrentUser That returned "Undefined" >set-executionpolicy Unrestricted -scope CurrentUser That gave me a warning which I clicked OK to accept once those two commands were executed I was able to run the script within the PowerShell ISE

Note from the Author or Editor:
Regarding the typo: Please, replace the text "PowerShell_Example.ps2" with "PowerShell_Example.ps1". Regarding the operating system privileges to execute PowerShell scripts: An appropriate warning should be added to the chapter. I will send the text through email. ML

Zack Jones  Apr 30, 2013  Aug 16, 2013
Printed, PDF
Page 481
Principals section, 1st paragraph

There isn't an SSISDB stored proc called "catalog.configure_server". Perhaps you mean "catalog.configure_catalog"

Note from the Author or Editor:
True. Any and all references to "catalog.configure_server" should be replaced with "catalog.configure_catalog" (that is, once on page 481 and once in the Index, on page 772). ML

Malcolm  Feb 07, 2013  Aug 16, 2013
Printed, PDF
Page 507
Top

Quick Check Answer reads "On the SSISDB, project, and package levels" It should read "On the SSISDB, Folder, and Package levels" Predefined reports are not available on the Project level.

Note from the Author or Editor:
Unable to check at the moment, but I suspect the reader is correct. Will respond with a more definite answer ASAP. ML UPDATE: The reader is only partially correct. The text of the Quick Check Answer should be changed to: "On the SSISDB catalog, folder, project, and package levels." ML

Malcolm  Feb 08, 2013  Aug 16, 2013
Printed
Page 508
Exercise 1, Step 1

In addition to re-running the script from chapter 5 code folder you also need to open the TK463Chapter13.sql file and run the first section to create the stg.SalesTerritory table. This step is missing from the instructions.

Note from the Author or Editor:
Exercise 1, Step 1 on page 508 should have an additional statement after the existing text "If you are missing the database objects from Chapter 5, “Designing and Implementing Data Flow,” execute the needed SQL code from that chapter to have all the stage and dimension tables available in the TK463DW database. " Should have an additional sentence at the end: Start SSMS and connect to your SQL Server instance. Open a new query window by clicking the New Query button. Select the TK463DW database in the database drop-down list. Create the stg.SalesTerritory staging table by executing the following SQL statement. CREATE TABLE stg.SalesTerritory ( TerritoryID INT NULL, Name NVARCHAR(50) NULL, CountryRegionCode NVARCHAR(10) NULL, [Group] NVARCHAR(50) NULL, ModifiedDate DATETIME NULL );

Zack Jones  May 02, 2013  Aug 16, 2013
Printed
Page 508
Exercise 1, Step 3

I use a named instance, so I always have to change the connection strings in the sample projects and click on objects to refresh Metadata (I'm not sure why that works, but whenever I get a "Metadata" error it seems the only thing I need to do is open the data flow object, click on "columns", and close it again.) I also have to change the debug property Run64bitRuntime to false for the project. I made these adjustments to the sample project in the Lesson 1, Starter Folder of Chapter 13 and followed the instructions in Exercise 1. Step 3 says I'm supposed to get an error and I did not. The package completed with success. But the reason was that there was no data in my staging tables after re-running the Chapter 5 code and the Create Table code for chapter 13. In step 1, please include information for populating the tables by running the FillStageTables package in Chapter 5. After filling the staging tables with data, I repeated step 3. The truncation error occurred in Get Customer Information (not Set Default Values) It's the same error message noted in Step 5 of Exercise 1, but with text referring to Get Customer Information instead of Set Default Values.

Note from the Author or Editor:
Change please the text in step 1 of exercise 1 from: If you are missing the database objects from Chapter 5, “Designing and Implementing Data Flow,” execute the needed SQL code from that chapter to have all the stage and dimension tables available in the TK463DW database. to: If you are missing the database objects from Chapter 5, “Designing and Implementing Data Flow,” execute the needed SQL code from that chapter to have all the stage and dimension tables available in the TK463DW database. Execute also the FillStageTables package to load the needed stage tables with sample data.

Anonymous  Aug 31, 2013 
Printed, PDF
Page 538
Exercise 1, Check for Data Quality Issues

In the list of tables to check for data quality issues, there is a duplicate. Nice if you enjoy the irony. :-)

Note from the Author or Editor:
Truly:-) Delete the last row, i.e. the last bullet on the page, where DimDate appears for the second time.

Malcolm  Feb 09, 2013  Aug 16, 2013
PDF
Page 559
Question 1 (top of page)

Since there is only one answer to question 1, the text "(Choose all that apply.)" should be removed.

Note from the Author or Editor:
Page 559, question 1 (top of the page), the text (Choose all that apply) should be removed.

Paul Neumann  Sep 18, 2013 
PDF
Page 573
Exercise 1, step 3 (bottom of page)

Remove the extra "DimDate" from the table list.

Note from the Author or Editor:
Page 573, last item in the bulleted list (DimDate), is repeated and should be deleted.

Paul Neumann  Sep 18, 2013 
Printed
Page 579
Introductory sentences in Exercise 1

It would be helpful to include the step "Turn Windows Features on or off" after the phrase "use Control Panel | Programs and Features" for Windows 7 users. It's not obvious, once on the Programs and Features page, what to do next, so the addition of the step to Turn Windows Features on or off would be welcome. From there, the rest of the options in step 5 are visible by expanding the nodes under IIS.

Note from the Author or Editor:
The last sentence in the opening paragraph for exercise 1, which is "If you are using Windows Vista or Windows 7, use Control Panel | Programs And Features instead of Server Manager.", should read "If you are using Windows Vista or Windows 7, use Control Panel | Programs And Features and the Turn Windows Features on or off instead of Server Manager.".

Anonymous  Sep 02, 2013 
PDF
Page 594
Step 10

Page 594 Step 10: "At the bottom of the page, click the Add Leaf Attribute button. Make the new attribute domain based and use the CountryRegion entity for the domain values. Do not change the display pixel width or enable change tracking. Save the attribute and then save the entity" Please specify a name for this attribute. I assume it should be named 'CountryRegion', so that it will then correctly show up in the Available Entities And Hierarchies area in step 26? page 595 step 26: "...Also drag the StateProvince and CountryRegion entities to form the hierarchy. Click the Back To Model View button in the upper-left corner, the button with the green arrow"

Note from the Author or Editor:
Step 10, which reads "At the bottom of the page, click the Add Leaf Attribute button. Make the new attribute domain based and use the CountryRegion entity for the domain values. Do not change the display pixel width or enable change tracking. Save the attribute and then save the entity.", should read: "At the bottom of the page, click the Add Leaf Attribute button. Name the attribute CountryRegion. Make the new attribute domain based and use the CountryRegion entity for the domain values. Do not change the display pixel width or enable change tracking. Save the attribute and then save the entity." And please, in the sentence "Name the attribute CountryRegion.", make the CountryRegion bold.

truncheon411  Jun 16, 2013  Aug 16, 2013
Printed
Page 617
ch.16, lesson 2, paragraph above step 1

A sentence near the end of this paragraph mentions that execute perms on mdm.udpSecurityMemberProcessRebuildModel is required to change the MDS sysadmin. However, it's not mentioned in the 2-step procedure that follows (which uses mdm.udpSecuritySetAdministrator). Are execute perms on both stored procedures required?

Note from the Author or Editor:
Yes, the procedure name in the last paragraph before the procedure steps at the bottom of the page should change from mdm.udpSecurityMemberProcessRebuildModel to mdm.udpSecuritySetAdministrator.

Ed  Dec 08, 2013 
Printed
Page 628
Quick Check Answer

Excel 2012 should be Excel 2010.

Note from the Author or Editor:
The Quick Check answer should be corrected from "Quick Check Answer &#9632;&#9632; Use Excel 2012 with MDS Add-in for Excel to update batches of data in an MDS model." to "Quick Check Answer &#9632;&#9632; Use Excel 2010 with MDS Add-in for Excel to update batches of data in an MDS model."

Zack Jones  May 09, 2013  Aug 16, 2013
PDF
Page 644
Exercise 2 Step 18

"Then enter the following string as the expression: \p{L}+\d\d@ADVENTURE-WORKS\.COM." should read "Then enter the following string as the expression: \p{L}+\d@ADVENTURE-WORKS\.COM."

Note from the Author or Editor:
The regular expression in step 18 at the bottom of the page, which is currently \p{L}+\d\d@ADVENTURE -WORKS \.COM should be modified to: \p{L}+\d@ADVENTURE -WORKS \.COM

ScottVK  Feb 05, 2013  Aug 16, 2013
PDF
Page 660
question 2 choice D

" Check the standard distribution of the values" should read " Check the standard deviation of the values"

Note from the Author or Editor:
Question 2, choice D, the text "Check the standard distribution of the values." shold be replaced with "Check the standard deviation of the values."

truncheon411  Jun 15, 2013  Aug 16, 2013
PDF
Page 674
Step 22

"On the Create Test Set ..." should be "On the Create Testing Set ..."

Note from the Author or Editor:
Page 674, step 22. text "On the Create Test Set page," should be replaced with "On the Create Testing Set page,".

Paul Neumann  Oct 16, 2013 
Printed
Page 726
Ch.19, lesson 3, exer.2, step 2

I am unable to complete this exercise. I installed the Windows 7 SDK, as specified on p.725, but it did not install the C# templates (they didn't show up in SSDT). After some research, I downloaded and installed Visual C# 2010 Express, and then discovered that SSDT would not run without Visual Studio 2010 sp1 (a 1.5Gb download). Since I (presumably) needed only the templates, I located them and copied them to the right folder. Then I uninstalled VC#, in order to get SSDT working again. The templates showed up in SSDT, but upon creating a C# class library project, Solution Explorer listed it as unavailable, with the message 'project file was unloaded.' So I reinstalled VC# and then downloaded and installed VS2010 sp1. SSDT now works, and the templates show up, but I still can't create a class library project (same message above). Is the full VS product is required to use these templates?

Note from the Author or Editor:
This is an oversight on my part. We need to amend the System Requirements section (Page xxvii) to instruct the readers that Visual Studio 2010 (or at least Visual C# 2010 Express) must be installed on the training workstation in order to perform Chapter 19, Lesson 3 exercises. I will send more detailed instructions via email. ML

Ed  Jan 19, 2014 
Printed, PDF
Page 728
second and 3d paragraph Deploy.bat

gacutil fails with "Failure adding assembly to the cache: Attempt to install an assembly without a strong name." There should be a reference to instructions for signing the assembly.

Note from the Author or Editor:
Additional steps must be added to Exercise 2 of Lesson 3, Chapter 19, instructing the student how to digitally sign the assembly.

Bodhi Densmore  Mar 27, 2013 
PDF
Page 728
Exercse 3, Step 5 (middle of page)

A friendly suggestion: the text should indicate that the batch file needs to be run with Administrator privileges. Here is a text example: "When you are ready, run the batch file as administrator to deploy the component."

Note from the Author or Editor:
The following statement should be added at the end of Step 5 (Page 728, Exercise 3): "If in your particular environment elevated privileges are required to run the gacutil utility, right-click the file in the Windows Explorer and then click Run as administrator to perform the operation." ML

Paul Neumann  Oct 28, 2013 
PDF
Page 728
Exercise 3, Step 4

Add a comment that the path to the "gacutil" command uses (3 in total) may need to be adjusted based on the readers operating system. For example, the portion of the path containing the "v7.0A" should be adjusted to "v8.0A" for Windows 8.0 users.

Note from the Author or Editor:
While it is virtually impossible to predict every single potential difference between the environment used for training kit development and the one(s) used by the readers, the reader is correct in pointing out this issue. I have prepared a new version of the command file ("Deploy.bat"); it now includes a comment instructing the users to correct the paths appropriately. I will send this file via email. ML [UPDATE, 2013-12-04]: The updated file has been received by the publisher on 27th November 2013, and should be available in the latest version of the companion content ZIP. ML

Paul Neumann  Oct 28, 2013 
PDF
Page 730
Question 1, 2, and 3

Question 1, 2, and 3 are missing the phrase "(Choose all that apply.)" and the end of each question.

Note from the Author or Editor:
The following text must be added after each of the questions 1, 2 and 3: "(Choose all that apply.)" ML

Paul Neumann  Oct 28, 2013 
PDF
Page 752
Exercise 2, step 4

The last sentence in step 4 directs the reader to select the "CustomersClean" table. This is incorrect. It should direct the reader to select the "CustomersDQSMatch" view. Figure 20-4 also shows the wrong table/view selection.

Note from the Author or Editor:
Page 752, step 4, last sentence, the text "CustomersClean table." should be replaced with "CustomersDQSMatch view." Please note that before next print, the figure 20-4 should also be replaced with a new one. For O'Reilly: please send me contact info to send the correct figure. Dejan Sarka

Paul Neumann  Oct 28, 2013 
Printed
Page 752
Ch.20, lesson 2, exer.2, step 4

This step specifies using the data in CustomersClean as the sample for testing a matching policy. Since the rows in this table are clean (and unique), no matches can be generated, regardless of the policy. Should the CustomersDQSMatch view (created in step 1) be used instead, since it seems to have been created for this purpose? Note that if this table is used, then the match results generated in steps 13 and 20 will be identical.

Note from the Author or Editor:
Yes, please replace the expression "CustomersClean table" in the last row of step 4 with "CustomersDQSMatch view".

Ed  Jan 16, 2014 
PDF
Page 763
Exercise 2 - Step 1

A step should be added before Step 1 or the following information appended to Step 1 with guidance on how to enable MDS integration with Data Quality Services. Since this had not been completed prior to this final chapter, the Data Quality group of the Master Data tab will not be activated within Excel. Here is suggested verbiage: Open the Master Data Services Configuration Manager console. You can find it in All Programs | Microsoft SQL Server 2012 | Master Data Services. Click "Web Configuration" in the left pane. In the Website drop-down in the right pane, select MDS. Lastly, click the "Enable integration with Data Quality Services" button in the bottom of the right pane. Click the "Exit" button.

Note from the Author or Editor:
Add additional text at the end of the intro text for the exercise 2, before the first step. Text should be: "If you did not enable MDS integration with DQS yet, the Data Quality group of the Master Data tab might not be activated within Excel. If this is the case, open the Master Data Services Configuration Manager console. You can find it in All Programs | Microsoft SQL Server 2012 | Master Data Services. Click "Web Configuration" in the left pane. In the Website drop-down in the right pane, select MDS. Lastly, click the "Enable integration with Data Quality Services" button in the bottom of the right pane. Click the "Exit" button. "

Paul Neumann  Nov 01, 2013