sql server dts: Hi,

I have set IMEX=1 and HDR=NO. I am referencing the excel columns as F1,
F2, etc...

The problem is sometimes in the excel files, not all the columns
referenced containes data. Thus I got the following error "No Value
Given For One Or More required Parameter" when the ExpandedColumnCount
in the excel file is less than the specified column.

Currently, I manually open the excel file, enter some data to the empty
columns, save the file, then delete the data I have just entered.
Anyone had any idea how to resolve this issue?

TIA.

-- Jordan

[quoted text, click to view]


As you loop through your Excel file, try looking at each Excel cell for
IsDBNull. I used the DataReader and have given you a sample of something I
am currently writing. Hope this helps. See the code sample below

--> Chiquita

Dim i As Integer
Dim myColumnValue as String
Dim myOleDBCommand As New System.Data.OleDb.OleDbCommand("Select * From
[Sheet1$]", myConnection)
Dim myOleDataReader As OleDb.OleDbDataReader = myOleDBCommand.ExecuteReader

myColumnValue = ""

Do While myOleDataReader.Read()

For i = 0 To myOleDataReader.FieldCount - 1

'Check to see if the value in the cell is NULL
If myOleDataReader.IsDBNull(i) Then
myColumnValue = ""
Else
'Since DataReader interprets the data, convert everything
to string values
myColumnValue = Cstr(myOleDataReader.Item(i))
End If

' DoSomethingWithYourColumnValue

Next
Loop

myOleDataReader.Close()
myConnection.Close()