How can I create XLS file in DTS
-
Hi,
How can I create *.xls file in DTS package (VBScript) on server where Excel Application is not installed ? I must export data in to *.xsl file daily and create file with current date each day.
Thank you in advance,
Gregory
-
Gregory,
You'll be able to do so. Even though Excel isn't installed, the Excel driver should be there.
For most consistent results, hop onto the server in question (either locally or via terminal services) and use the import/export wizard to create a DTS package where the destination is Excel 97-2000.
You can execute the package, as well as save and schedule it too.
Cheers,
- Mark
Cheers,
- Mark -
Hi Gregory,
quote:
How can I create *.xls file in DTS package (VBScript) on server where Excel Application is not installed ? I must export data in to *.xsl file daily and create file with current date each day.another way could be to export into *.csv format, which could easily be read by Excel
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Gregory,You'll be able to do so. Even though Excel isn't installed, the Excel driver should be there.
For most consistent results, hop onto the server in question (either locally or via terminal services) and use the import/export wizard to create a DTS package where the destination is Excel 97-2000.
You can execute the package, as well as save and schedule it too.
Cheers,
- Mark
Hi Mark,
I'm not sure, but when I use import/export wizard and Excel as destination, the destination file must exists. So when each day I must create a new file for example ex20030814.xls, ex20030815.xls etc., Excel as destination (even with Dynamic Properties Task) is not accurate in this situation ...
Gregory
-
The destination file doesn't have to exist. In my testing I created one from the DTS package.
Cheers,
- Mark
Cheers,
- Mark -
Look at some of the articles here http://www.sqldts.com/default.aspx?6,101,101,5,1 This should give you some ideas on what you're looking for.
David
-
quote:
quote:
Gregory,You'll be able to do so. Even though Excel isn't installed, the Excel driver should be there.
For most consistent results, hop onto the server in question (either locally or via terminal services) and use the import/export wizard to create a DTS package where the destination is Excel 97-2000.
You can execute the package, as well as save and schedule it too.
Cheers,
- Mark
Hi Mark,
I'm not sure, but when I use import/export wizard and Excel as destination, the destination file must exists. So when each day I must create a new file for example ex20030814.xls, ex20030815.xls etc., Excel as destination (even with Dynamic Properties Task) is not accurate in this situation ...
Gregory
Does it have to be done through DTS? the follwoing link explains how to do it through T-SQL.
http://www.sqlservercentral.com/scripts/contributions/763.asp
Tim
-
Tim,
quote:
Does it have to be done through DTS? the follwoing link explains how to do it through T-SQL.this is certainly a solution, too. I use those sp_OA* myself here quite often.
However, there are some things to consider.
1. You need to have Excel installed on the SQL Server box. At least the Excel Object library.
2. So this might be a licensing issue. I'm not sure if the olb file is redistributable, meaning you might have to buy an extra Excel license for the SQL Server.
Apart from this it works really great.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Tim,quote:
Does it have to be done through DTS? the follwoing link explains how to do it through T-SQL.this is certainly a solution, too. I use those sp_OA* myself here quite often.
However, there are some things to consider.
1. You need to have Excel installed on the SQL Server box. At least the Excel Object library.
2. So this might be a licensing issue. I'm not sure if the olb file is redistributable, meaning you might have to buy an extra Excel license for the SQL Server.
Apart from this it works really great.
Cheers,
Frank
To be honest, Frank I haven't actually tried the script myself
, but don't tell anyone. I saved it because I knew it would come in useful some day (not quite sure when), but like my dad says, never throw away something you could sell later. So, GWozniak, that'll be £500 please!!! -
Start with an axtivex script to genterate the filename for the day
eg filename = "ex" + date + ".xls"
Yor next step will be an FTP Task where the source file is a blank excel file and the destination Filename set by the previous avtivex script (Makes a copy of a blank excel file with the filename) then run your tansformation.
-
You can make this relatively straight forward. I'm assuming you're running a daily report of some sort and you want a new date stamped file every day.
1) Manual step - do once: Copy Excel file to server you're running DTS on.
2) Get the current date and assign to global variable (I'm doing this in an Execute SQL task and then assign the value to a global variable using an output parameter)
3) Copy the file created in step 1 using th e following activex:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile("C:\Template.xls")
f.Copy "C:\WorkingTemplate.xls"
Main = DTSTaskExecResult_Success
End Function
4) Use this as "WorkingTemplate.xls" as your destination for a datapump. The name never changes, so you don't have to do this dynamically (another option).
5) After updating the "WorkingTemplate.xls" file, copy it and add the date from the Global variable (step 2):
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile("C:\WorkingTemplate.xls")
f.Attributes = 0
f.Copy "C:\Report_" & DTSGlobalVariables("Today").Value & ".xls"
f.Delete
Main = DTSTaskExecResult_Success
End Function
Signature is NULL
, but don't tell anyone. I saved it because I knew it would come in useful some day (not quite sure when), but like my dad says, never throw away something you could sell later. So, GWozniak, that'll be £500 please!!! 
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply