Problem importing Excel data with DTS

[nwm76]

Hi,

I've got an instance of SQL Server 7 where I'm using a DTS package to import some excel data into a table in my database.

The Excel sheet is a simple 3-column table with the data beginning in row1. However, instead of importing row1 as data, DTS is trying to force the values in row1 to be the column names of the Excel file, therefore I lose the first row on import.

I found the following ActiveX script on the net, which is supposed to prevent this behaviour, but it doesnt appear to be working.

How do I connect the ActiveX Script task to my Excel connection to make this work?? The weird thing is that I've had this working before, and I cant work out how to get it working again.


Any ideas, much appreciated!

Cheers
Neil

Function Main()

	dim pkg
	dim conXL

	set pkg = DTSGlobalVariables.Parent
	
	set conXL = pkg.Connections("Microsoft Excel 8.0")

	conXL.ConnectionProperties("Extended Properties").Value = "Excel 8.0;HDR=NO"

	Main = DTSTaskExecResult_Success
End Function

 

[Catadmin]

This may sound like a silly solution, but have you tried inserted a row above the first one with column header information?

ISTR some sort of DTS transformation task that had the option of turning on or off the first row as Column Header, but for the life of me, I can't remember where I saw it... Or even if it was a SQL App I saw it in.

Have you tried using the DTS Import Wizard from SQL rather than designing a package via the Data Transformation group or the above code?





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???