SSIS: Loading EXCEL into SQL Server - Cells w/ Data Load a..

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_dcus_nus_w.htm
HTTP downloaded using the "Download Series History" link.
into SQL Server 2005 using SSIS.

In two cases:

1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
AND
2. "Script Task" code:
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
Gasoline and Di$]", cnn)
Dim ds As New DataSet("ExcelFile")
Dim column As Int32
Dim row As Int32
Dim cellData As String

da.Fill(ds)

For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
Then
cellData = "NULL"
Else
cellData =
CType(ds.Tables.Item(0).Rows(row).Item(column), String)
End If
MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
Next column
Next row

>>> The second, third and fourth colunms (among others) of the Speradsheet
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.

"Sam Wilson" wrote:

> Or just add IMEX=1 after HDR=Yes in your code...
>
> I should read things before I reply.
>
> "Sam Wilson" wrote:
>
> > Are the columns that aren't importing properly a mixture of numbers and
> > strings?
> >
> > If you have numbers in a column, even if they're in excel as strings, then
> > the non-numeric values are skipped by SSIS, it took me ages to find that.
> >
> > In the design studio, right-click on the connection in connection managers -
> > go to propertioes and change the connection string to include "IMEX=1"


"ITContractor" wrote:

> Greetings,
>
> I've been trying to load the EXCEL Spreadsheet:
> http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_dcus_nus_w.htm
> HTTP downloaded using the "Download Series History" link.
> into SQL Server 2005 using SSIS.
>
> In two cases:
>
> 1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
> AND
> 2. "Script Task" code:
> Dim cnn As New
> OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
> Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
> Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
> Gasoline and Di$]", cnn)
> Dim ds As New DataSet("ExcelFile")
> Dim column As Int32
> Dim row As Int32
> Dim cellData As String
>
> da.Fill(ds)
>
> For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
> For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
> If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
> Then
> cellData = "NULL"
> Else
> cellData =
> CType(ds.Tables.Item(0).Rows(row).Item(column), String)
> End If
> MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
> Next column
> Next row
>
> >>> The second, third and fourth colunms (among others) of the Speradsheet
> return NULL values although the Spreadsheet obviously contains data
> in
> those cells.
>
> Any ideas are appreciated.
>
>