CHAPTER 3-Lesson 2: Deploying SSIS Packages, exercise 2 Deploy your Packages by using an Installer Kit

I'm using the self paced training kit Exam 70-448: MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

I had problems and errors completing exercise at the end of step 3-verify the packages were deployed.  I was abIe to run the installer kit via {project name}.SSISDeploymentManifest and was able to connect to the Integration Service in SSMS.  However when I tried to expand the Stored Packages under the MSDB folder, I received a message that the system couldn’t find the files and the message indicated I didn’t have a default instance installed on my server.

After reviewing my installation I saw my default MSSQLSERVER instance was under a sql 2008 express version and I’m working with sql 2008 R2 developer.  To correct this issue I had to uninstall the sql 2008 express version as follows:

• Go to: Start Menu>Control Panel>Programs and Features>Microsoft SQL Server 2008>Uninstall/Change
• Wait for the SQL installer to open and select the MSSQLSERVER instance from the dropdown and uninstall. (Note: I uninstalled several instances I wasn't using.)
• Then I reran the SQL Server 2008 R2 Development Studio installer and installed the default MSSQLSERVER instance.

So far everything is checking out. It took about 4 hours to troubleshoot.  Moving on.

Chapter 3, Lesson 1: Using Package Configurations and Expressions, Exercise 3

I'm using the self paced training kit Exam 70-448: MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Chapter 3, Lesson 1: Using Package Configurations and Expressions, Exercise 3: Use a Property Expression to Update a Connection.  Debug notes.

Using the SSIS package provided Import_Excel_Files.dtsx, I needed to modify the following:

• Control Flow, ForEach Loop Container; Edit- Collection, Enumerator configuration- Browse to the correct folder containing chapter examples.
• Connection Manager, AdventureWorks2008. Edit- I have several instances installed so [local] as a server name doesn’t work.  I need to point to the ‘server\instance’
• Connection Manager, Archive.  Edit- browse to the correct folder containing chapter examples.
• Connection Manager, Excel Connection Manager.  Edit- browse to the correct folder containing chapter examples.
• Under the Data Flow I needed to edit OLE DB Destination to [dbo].[stg_Customer]

On run I kept getting this error;

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

FIX- In the Solution Explorer right click on the project name and go to properties. Click on the ‘Debugging’ page and under ‘Debug Options’ switch ‘Run64BitRuntime’ to false. 

I kept getting an Failure error on the data conversion transformation in the data flow.  I examined the excel file and saw some non-numeric entries for the age.  This lesson doesn’t seem to be concerned with that and just wants to have the package run to completion.   I did a right click edit and clicked the ‘Configure Error Output’ and on the ‘New_Age’ row, changed the option for ‘Error’ to Ignore Failure.

Practice creating and configuring precedence constraints Exercise 1 page 77

I'm using the self paced training kit Exam 70-448: MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Page 77 Chapter 2 Lesson 2: Identifying Package Status, Enabling Logging, and Handling Task Errors, Practice exercise 1-Set up Constraints-Step 5 reads;

"5. In the Precedence Constraint Editor dialog box, change the Value drop-down list to Success, and then click OK to save the changes."

If you follow the logic through the rest of the exercise you should actually change the Precedence Constraint value in step 5 to Failure and not Success.

Chapter 1, Lesson 3, Pratice Exercise 2. Create a Data Flow Destination. Broken SQL Statement

I'm using the self paced training kit Exam 70-448: MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

In my version of the book on page 52 the SQL command text for the Customer Source data flow sample is broken. The table join fields at the bottom of the statement don't exist.

Select convert(nvarchar(15), SC.
AccountNumber) as CustomerAlternateKey,
C.Title, C.FirstName, C.MiddleName,
C.LastName, C.Suffix, C.EmailAddress,
C.AddressLine1, C.AddressLine2,
D.BirthDate, D.MaritalStatus,
D.YearlyIncome, D.DateFirstPurchase,
D.Gender, D.TotalChildren,
D.NumberChildrenAtHome, D.Education,
D.Occupation, D.HomeOwnerFlag,
D.NumberCarsOwned
from Sales.vIndividualCustomer C
inner join Sales.Customer SC
on C.BusinessEntityID = SC.PersonID
inner join Sales.vPersonDemographics D
on C.BusinessEntityID =
D.BusinessEntityID

To fix this I created an new SQL statement in SSMS for the database in question and pasted in above sample.  I navigated to each table and inspected the fields.  I found that the BusinessEntityID and PersonID should all be changed to CustomerID as follows;

... from Sales.vIndividualCustomer C
inner join Sales.Customer SC
on C.CustomerID = SC.CustomerID
inner join Sales.vIndividualDemographics D
on C.CustomerID = D.CustomerID

A very simple fix.

AdventureWorks2008 Doesn't Exist

I'm using the self paced training kit Exam 70-448: MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

When you download the Adventure Works database sample at http://msftdbprodsamples.codeplex.com/releases/view/55926, you'll find it doesn't include the AdventureWorks2008 sample being called for. No big deal. It's the same database as AdventureWorks with some date changes.

I did my first pass through the book pointing to AdventureWorks in my connection strings and when later SSRS lessons were calling for dates that were out of rage (2004-2008), I ran a quick query against the fields in question in SSMS as follows:

Use [Database]
Go

Update [Table]
Set [xField] = DATEADD(year,3,[xField])

This run through the book I used the export wizard against AdventureWorks and created AdventureWorks2008. Now I'm pointing to AdventureWorks2008 in my BIDS connection strings and I'll update dates when needed.