I am trying to import some rows from a Excel sheet to a SQL Server table, but
when the first 200 rows on the sheet does not have values for a column, the
dts asumes that the whole column does not have values and import a NULL value
into the table, even if the column has values on the 250th row. If the Excel
sheet has a value on that column on the first 200 row, the DTS imports the
data right.

What can I do to make my DTS work right no matter if there is a value on the
first 200 rows or not?

Thanks for your help

Have a look at this article and see if it helps

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"John Ballesteros" <John Ballesteros (AT) discussions (DOT) microsoft.com> wrote

John,

You could cause the driver to sample all available rows instead of a small
number by entering 0 (zero) as the value of
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows in
the Registry.

281517 PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer
Overflow
http://support.microsoft.com/?id=281517

Without experimenting, I'm not certain whether "Import Mode" would resolve
this particular problem, but that involves adding "IMEX=1" in the Extended
Properties string for the Jet provider.

194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
http://support.microsoft.com/?id=194124

Hope this helps,

-Doug


--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.