Watch out setting the row number to a large number as dts will process theses rows.

IE - Will take longer and fill your database up with empty rows.

May also cause...

If you know the data in a column that identifies the row as a header, then you can use skip row.

However, you have to sepcify all DTSSource and DTSDestination details...

It is true that the dts components for 2005 does in fact cause the screw up.

However, you can get over this by installing sql server 2000 service pack 4. Worked...

In what respect?

Do you want to be able to execute dts or create a dts package from scratch etc....

In order to create the excel object on the server, excel needs to be installed on there.

This may be the reason you can run locally, but not on the server.

If...

For a good webiste with some real-life examoples to follow:

http://www.sqldts.com/

Also contains some known fixes for dts anomoloies - eg null values when importing excel as excel looks at...

Don't use an 'Execute Process Task' in this instance. If you are going to be supplying some parameters then use a sql task instead.

exec master.dbo.xp_cmdshell 'YOURBATCHFILENAMEANDPATH' @YOURDATEVARIABLE

That's all there is...

This will do it. Just needs tweaking with your directory and file name. This example for a sql dump file:

---- Determine lastest file

declare @FileName varchar(100)

create table #dbdmp ( FileName...

In your activex script try using -

excelapp.displayalerts = false

Can't think of anything else that would stop it running. A new install may be the best option.

Also check to see what account the services are running under, especailly the...

Sometimes you will find that dts remembers the last time that it ran and uses that row as the startting point even though it has cleared out the spreadsheet. This...

You are in luck, in that dts treats excel as it would a table (well, very nearly).

Before you start the transformation, add a sql task with the following code.

DROP TABLE...

Check under 'Support services' node in enterprise manager and make sure that the distributed transaction co-ordinator is running.

Barring that, you may be looking at a re-installation.

Start by looing at the number of the dimensions being used and the number of members within them. It could be that you are using a dimension that contains thousands...

First off let me state that I don't think dts is the best tool for the job.

But....if you want to persevere, then this link and associated link may help...