In SQL Server 2000 DTS there is an Extended Connection properties window that you can set the IMEX property for importing Excel spreadsheets into a SQL Server table. Does anyone know where this is in SQL Server 2005 SSIS? Thanks,

Under the Data Flow Sources, you will find Excel Source. You can double click this and customize the settings to import an excel spreadsheet into memory. From there onwards, you can drop in a SQL Server Destination task from the Data Flow Destinations section and map your columns to the destination table that can also be set.

Yes, I knew that. The problem is when the data changes type in a column the import converts them into null values. The property IMEX fixes this problem. In SQL Server 2000 DTS there was an extended properties section where you could set this property. I can not find this section in SSIS. Here is a link that describes the problem: http://dotnetjunkies.com/WebLog/amar/archive/2005/03/11/59990.aspx

The way to add the option is as follows: Add an Excel Connection Manager to the package. Selecting a sample file. Create a parameter at the package level called ConnectionString and assign it the value:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";Replace <FileName> with the fully qualified (ie path and filename) ofthe sample Excel file from 1. In the properties for the Connection Manager open the Property Expressions Editor Select "ConnectionString" in the Property columns dropdown list Add @[User::ConnectionString] to the Expression Column Click Ok This will then override the default Excel connection string everytime it runs. You can override the data source component of the string by setting the Excel File Pathof the connection manager. Hope this helps.

Here are some examples and problem descriptions with mixed datatypes and IMEX: http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.htmlPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com