Excel to Sql

Hi,
I want to convert excel sheet data to sql database.I have completed it using OLEDB connection and sql query.I retrieved the data from excel and bind the data into dataset using OLEDataAdapter and then dataset content will be displayed on grid.If the excel sheet data is of same datatype means it is working properly.See the excel sheet example...

Name Mark1 Mark2
Ram 87 76
somu 45 65
james 65 98

When converting this excel to sql it is working properly..
See the problem below..

Name Mark1 Mark2
Ram 87 76
somu 45 AB
james 65 98

when i am giving 'AB' to mark it shows null value at that specified column(ie,blank space on grid for the data 'AB').
This is my problem...How can i rectify this...Any alternative solutions...

You need to check your database column's datatype. The problem you are running into is most likely the target column is a numeric column.

Let me know whether you can fix the problem by change the data type of the column, keep the data type consistent in your code too.

|||May i know,the way to convert excel t sqlserver database table|||You can simply use Import/Export Wizard in SQL Server

Excel Pivot Table and Time Dimension

I’m using the OLEDB Provider for Analysis Services 9.0 in Excel 2003 to get to my OLAP cubes in SQL Server 2005 Standard SP1.When I use Time as a Page filter in an Excel Pivot table, the dropdown looks like this (May_2006 is the default period):

-2006
-Qtr2_2006

+May_2006

+Apr_2006

+Jun_2006

+Qtr1_2006

+Qtr3_2006

+Qtr4_2006

+2004

+2005

I checked the field settings in Excel and it is using the data source order option.When I browse the dimension, the Time members are shown in chronological order.I’ve concluded that this behavior is a result of setting the default month in the Time dimension.Anyone know if this is simply the way it is when working with a defaulted Time dimension in Excel?

Moving to SQL Server Analysis Services forum.

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul

|||You should use a data conversion to convert the strings to unicode