August 15, 2006 at 2:13 am
Morning,
I am trying to export data from a SQL Server 2000 Dbase to Excell using DTS, but am not getting very far.
I have created two connections in DTS one pointing to the Dbase. The other points to a spreadsheet which has been saved to a different server.
Have joined these with a Transform Data Task Properties. The source SQL query works fine and I can see data. The destination tab shows sheet1$ and the column names. It is worth noting that if the spreadsheet is open then I cannot open the Destination tab, indicating/confirming that DTS can access the spreadsheet.
The Transformation Tab is set to Write File and Overwrite if file exists. Am intrigued to note that in exporting 4 attributes that I have to use 2 transformations of 2 attributes each. The Directory points to the correct Directory (see above test) and I have tried both ANSI and UNICODE options.
The DTS says that it runs OK but no data is put in the spreadsheet. Can anybody through any light on why the data is not shown? Trust the above exxplanation is clear.
Thank you
Colin
August 16, 2006 at 3:09 am
Hi, I have not tried exporting data to a spreadsheet on another server but do, regularly, export to a spreadsheet on the same server as SQL Server. I then use the xp_sendmail procedure (a further step in my DTS package) to mail the spreadsheet file to any number of recipients.
i don't know if this is of any further information but I have noticed that on a number of occasions when exporting data to a spreadsheet the data can get screwed. Data moves one or more columns to the left where records contain NULLs. This doesn't happen every time and would seem to be related to the installation of Excel. I must admit I have not investigated this fully, it's more of an observation.
August 16, 2006 at 5:33 am
Hi Colin
You need to check first your server because DTS will create excel file on local host.
So just search your excel file on local server.
I am running a DTS package which transfer csv file to my clients computer and every time before transfer it remove the old file and then rewrite it.
i am doing this with FTP.
Just check first is your excel file is on local server or not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply