DTS- excel option missing

I am a senior DBA using SQL Server 2000.

I just did a clean install of SQL 2000 Personal edition. I am trying to DTS an excel file in but the excel icon is not there!!!!! There are the other excel ODBC sources but they each require me to setup a DSN (not normal!!!)

Anyone had this problem before? I am uninstalling and reinstalling.

-KevinI don't understand...DTS in or out?

Are you using the wizard?

When open the menu option connection, what do you see?|||I am trying to import with DTS.
When I choose the "source" drop down list I am expecting to see a little excel icon with Excel 97-2000 next to it.

I see a bunch of data direct closed icons and the excel treiber , and the WINSQL excel workbook. All of those options require me to define a DSN.

The normal excel option just lets you choose the excel file.

?!?!?!?!?|||So you're in EM and you right click on DTS and SELECT

>>ALL Tasks >>IMPORT DATA...

Then you use the wizard and change the data source drop down...

and you don't see the teal "X" for Excel?

It's half way down the list...

What if you try to build one from scratch?|||No luck.

I am going to try to do it with Informatica.
I applied mdac 2.8 and SP3a.|||Do you have excel installed on the local machine?|||Yes. Thanks for your help. I found another work-around using Erwin instead of SQL Server.
Thanks anyway.

-Kevinsql

DTS- Destination Column Name has spaces causing error

I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?

I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.

When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Use square brackets ( [ ] ) , instead of double quotes.|||

I tried this in a sql query against the Advantage ODBC and it failed. I believe this is a limitation of the ODBC drivers for Advantage.

The issus that the column names are causing this to fail inside the DTS Transformations themselves. Anyone know where I can edit the Destination column names so that I can wrap them with quotes or brackets.

Thanks

Donnie

|||

Have you tried enclosing the column names in single quotes instead of Microsoft brackets?,

i.e. SELECT 'My Column' FROM MyTable

|||

Yes tried single quotes.

If I try Insert into TableName ("Last Name") values ('Test') with the Advantage ODBC, it works. Issue is, I need to pull from SQL and push into Advantage. When I try with with transformations between the source and desitination, I get the missing closing ")" error.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Thanks for the help

Donnie

|||

This forum is for SSIS, not DTS.

You'll probably get better assistance on the DTS newsgroup.

|||Thanks DD. Got it over there as well as Google forums.|||Bumpsql

DTS- Data Driven Query Task

I use a data driven query task to perform row updates along with appending row data to a text file. In the source I use 'select top 1000 field1, ..., fieldn from table'. The package hangs when executing the data driven task. If I reduce the return count to 'select top 100...' the package execute with no problems. I can do that 10 times, with no problems. But anything more then 100 will hang the package at that task. Please help!

Thanks,
MoniqueTake help from PROFILER and see where it hangs.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP.

The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.