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.