Writing to Excel

Question

Post reply

Writing to Excel

rocker333

Ten Centuries

Points: 1315
More actions
September 26, 2006 at 4:02 pm

#71027

I have a Dynamic Properties Task supplying the path and file name of an Excel spreadsheet....I'm trying to pump SQL server 2000 data to the Template copy...When I create the pump, it writes the new table, setting the fields...no problem. When I run the package, it can't find the spreadsheet. I've narrowed the failure to the change of spreadsheet. Even though the path and file name are correct it fails. If I leave the spreadsheet there without replacing it the pump works. Any ideas how to get around this? thanks

A.S.

MCSE, ASE, DBA

Webmaster

Applications Developer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RoelofK68 SSC-Addicted Points: 436 More actions · Answer 1

Do you have a "create table" SQL task before the pump task?

<hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

Roelof
<a href='http://' class='authorlink' target='_blank'></a>

--There are only 10 types of people in the world. Those who understand binary, and those who don't.

PurpleLady SSCertifiable Points: 7698 More actions · Answer 2

We have several tasks of this sort and in the package we first delete the current spreadsheet, next recreate it using an ActiveX task and then populate it. One gotcha is that pop ups can occur depending on the create task (there are time when we delete individual sheets since our default is set to 3 and we only want one). To get around that we use appExcel.DisplayAlerts = False in the create script.

rocker333 Ten Centuries Points: 1315 More actions · Answer 3

Let me try to explain what I'm doing a little better.

I have a spreadsheet named Template.xls which is already setup with the table names.

There will be many spreadsheets created when this package runs.

A task makes a copy of the template and gives it a new file name.

The path and new file name are written to a global variable.

A Dynamic Properties Task supplies that global variable to the excel spreadsheet connection.

Finally the data pump writes data to the dynamic connection.

So you see when I run the package it creates a copy of the template whose path and file name are supplied for the data pump to send to. But it fails saying it can't find it.

If I go back to the pump, open it, it now finds it.

Thanks for your thoughts

P.S. Anyone else notice what a hog this site has become? Takes me forever to get anywhere on here...

A.S.

MCSE, ASE, DBA

Webmaster

Applications Developer

Lisa Slater Nicholls Hall of Fame Points: 3439 More actions · Answer 4

Sounds like it might be a rights problem.

When you create the copy of the template, who is the owner/creator of the original template? Who is the owner/creator of the copy?

When the pump runs un-attended by you, under whose auspices is it running?

>L<

rocker333 Ten Centuries Points: 1315 More actions · Answer 5

I don't think security is the problem...I'm running the package from within the package while its open. I'm also executing steps that fail in the package. thanks though...

A.S.

MCSE, ASE, DBA

Webmaster

Applications Developer

PurpleLady SSCertifiable Points: 7698 More actions · Answer 6

Do you have any precedences on the data pump? If not, try setting one using the copy task as the source. I had problems this past week when the precedence was not set on data pumps. It worked fine for the developer because they were stepping through the package, but when it was put into production - kaboom!