Simple DTS export to Excel

[Eyespi20]

I've created a job through the DTS wizard to export the results of a query to an excel file. I created the excel spreadsheet on the server/disk that I want it to put the resultset in and executed the job. It SAYS that it was successful, but when I open the spreadsheet there is no data in it.

When I execute the query in QA, I have one record.

Can anyone help me get this resolved?

Thanks!

Margaret

 

[yksvaan]

You wouldn't happen to have in Task's options First row and Last row 2, or something else than zero in them. If so, zero them and try again.

Is your resultset supposed to be only 1 row ?

Cheers

 

[Eyespi20]

Not sure about the option thing I don't think I had options to set. Where would I look to check that out? As I said, I used the wizard to set this up.

The result set will vary in number of rows -- yesterday, for example, there were 100+.

I was using a SP with a scheduled job to produce the query results, but the enduser prefers a spreadsheet, so this was my attempt at creating that solution.

 

[Eyespi20]

Well do I feel stupid!:~/

The package is adding a worksheet to the end of the spreadsheet called results. I was looking on the worksheet I set up as the template with the headers.

I need new glasses :

Thanks for your time and help!

Margaret

 

[yksvaan]

Yes, transform to excel always adds new rows, it doesn't rewrite the file. And, you can't delete the OS file, executing Transform Data Task won't create a new file. If you want to empty what the file includes, it can be done with a few steps.

Cheers

 

[Eyespi20]

What I do need to do now is to tweak my query a tiny bit, but I can't find how to do that without recreating the whole job. Any tips?

Thanks!

Margaret

 

[yksvaan]

The import/export wizard doesn't automatically save the package. If you just choose Run immediately and didn't save it, it is lost. If you saved it as local package you would find it in EM from Data Transf... > Local packages, but this you probably know already. if you saved it as structured file, right-click Data Transf..., choose Open package and navigate to folder the .dts file is.

Cheers

 

[Eyespi20]

I don't have the option of "open" just design, execute and delete. Design opens this flow-chart looking window and if I right click on the icon for the database the query is run on, it only gives me the ability to alter the connection, not the query.

Thanks!

Margaret