Excel columns to a temp table in SQL

I would like to transfer all the cells of an excel sheet to a temp table
in such a way that there is no data loss and no rows of columns of data
are made to Null or omitted.
I am keeping the datatypes of columns in the temp table as Varchar(255).
Thanks
Clayton.Use the Import Export wizard to do this for you. It will create an SSIS package that you can re-use and adapt to meet your needs. If it needs amending then please reply here asking for help and please try and be more specific.

Regards
-Jamie|||

I mean to say that when i transform a excel thru an excel source to a SQL destination database the problem is that the columns which are teh minority datatype in any row which contains a majority of integer numbers get passed
as NULL.how do i prevent this?

eg: suppose in a column named Year i have and i enter 2000,20004,2005,2006,abc,2003,2002

then this column with abc goes as blank i want to prevent this and i want abc to go to the SQL Destination DB.

Thanks
Clayton

|||I have just reproduced this error using the import/export wizard.

The wizard produces a SSIS package.
1. Open it up.
2. Open up the data-flow
3. Right-click on the source adapter and select "Show Advanced Editor..."
4. Click on the "Input and Output Properties" tab
5. Expand "Excel Source output"

You will see that the datatypes of the columns are defined in here. Under "Output Columns" change the datatypes to whatever you require (e.g. DT_STR with a langth of 255)

Hope this helps

-Jamie|||This is the oldest Excel driver problem in the book, and is documented in the BOL topic on the Excel Source (which is currently being expanded with still more "known issues" information).

If you would like to write to me directly, douglasl@.microsoft.com, I will send you a Word document that discusses at greater length all the potential known issues with Excel in SSIS.

Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data types of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values in fields that contain data of the other type. Most cell formatting selections in the Excel worksheet do not affect this data type determination. As one possible solution, you can use an OLE DB Connection Manager instead of the Excel Connection Manager, and modify this behavior of the Excel ISAM driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties on the All page of the Connection Manager dialog box. Use a semicolon to separate this name-value pair from the Excel version specifier. For more information, see PRB: Excel Values Returned as NULL Using DAO OpenRecordset.

-Doug