DTS fails when no excel file is found
-
Hello,
I have a SQL Server 2K and want to eport the data from the last week into an excel file.
So far I've managed to define a DTS package, with automatic file name creation, based on the actual week number.
But if I want to test this, it doesn't work anymore when I manually remove the destination excel file.
Any idea what I'm doing wrong ?
The error I'm getting is :
'The Microsoft Jet database engine could not find the object 'New_Table$' ! Make sure that it exists and that you spell it's name and path name correctly.
Any suggestions would come in handy.
Regards,
Tom
-
Try using the undocumented sp
xp_fileexist
There is various sites where you read up on the usage
-
Hi,
I've looked at other threads regadring DTS packages and excel data.
However, my specific problem is that the DTS package must be able to create a new excel file.
With the dynamic properties and a query I build up the excel filename where the data must be transferred to.
Every week I write the data from the last week into an excel file. Using the dynamic properties I create an excel filename where the weeknumber is included.
When I do this the first time, it works quit good. But when I want to test for the next week (change system time to test this)I get the error I specified in my first post.
I've tried a few things, but I'm struggling to get this specific job done.
Any ideas or suggestions would be very welcome....
-
To get around this particular issue I think I would always write to a standard file of the same name then rename the new file to what ever it is you want. Not very elegant but will be a lot more robust.
Cheers
Rob.
-
Hello Grasshopper,
Meanwhile I've been busy figuring thing out with DTS packages.
I've got a test scenario that's working fine with only 1 bug which I still have to figure out.
What I've done ?
First an activex script is opening the excel file 'FURNACE_LOAD.XLS' and then the data from row 3 up to 16000 are selected and deleted.
When this script has completed, a data transfer is performed from a query that returns the data from the last 7 days and puts this data in the excel file 'FURNACE_LOAD.XLS'
Next, and again only after the data transfer is completed, another Activex script runs and this one copies the file FURNACE_LOAD to FURNACE_LOAD_WEEK_xx (xx being the week 1 through 52)
This works very good. HOWEVER Ive got one bug.
In each weekly generated excel file, there will be one row that remains the same. Row 2. The reason I need to keep this row is that the DTS package changes the data fields from eg double to varchar. And when this occurs, all my numbers are shown as text, which cannot be approved by the client.
It seems that the DTS package gets it's data from the excel table layout.
Any idea how I can get rid of the second row of data without losing my data definition ?
Please advice,
Regards,
Tom
-
SOLUTION found !!After I've inserted the data from the database, I run an activex script that is copying the file to another file with the weeknumber in it's filename.
After I've copied the file, I open the file and just delete the second row.
This way I have the correct data that the customer wants to see.
I'm still wondering why that bug occured (if no data is present in the excel sheet all the sql server data is treated as text) , but I've got a solution that's working very good and is reliable.
If anyone has encountered this before, please let me know what you did to work around the problem.
Regards,
Tom
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply