DTS to Excel Spreadsheet
-
I have a DTS package that I need to run and basically create a new excel file everytime it does. What I have now is just appending the data to the previous set of Data...anybody have any ideas here?
Marty
-
Could you explain about the requirement a little bit more?!
If you have to import data from the EXCEL file to a table, you can do that. Or if you want to generate the an outfile from the appended data you can that as well.
.
-
add a SQL task pointed to the Excel sheet and do a "drop table" with the table name. Basically in your transform, on the destination tab, select "create", then copy the code and past it into a SQL task for create and a 2nd task for drop (edit the sql). Run the drop, the create, and the transform.
Once in awhile this screws up and I delete the excel sheet and rebuild it and it seems to work fine.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
If this is a job you can also make the drop file as a seperate job task run xp_cmdshell 'del yourfilename.xls'
John Zacharkan
John Zacharkan -
I believe I have the same problem. We (?) are trying to export to an Excel file with a name that is depending on a query (on an SQL DB) and a sheet name that is depending on another query (on an SQL DB). The problem is that it seems to me like DTS just can assign static names (assigned at design time) to Tables/Sheets when using Excel data sources/destinations. Is that true? If not, could anyone help me/us (?) solve this problem?
Clearification:
1 SQL OLE DB connection
1 Query that needs to be put in an ExcelFile with a custom name (different everytime) in an Excel sheet with a custom name (different every time)
I would be very happy if someone could help me/us (?) with this problem?
Regards, Hanslindgren
-
Although it's not a direct solution to your problems, somewhere I have found the follwing code. It uses the T-SQL approach. You have access to Excel's object catalog and can virtually do what you want.
/*
Name: Write to Excel from T-SQL
Description:The module opens/writes into Excel files directly from SQL server
All the parameters of this module could be provided automatically. In this way
whole procedure could easily be put SP, function, etc.
Inputs:File name of the excel document.
Returns:The query result, written directly in Excel document!
*/
DECLARE @desc varchar(255) -- Fehlerbeschreibung
DECLARE @Counter int
SET @Counter=0
DECLARE @ExlValue varchar(100)
DECLARE @ObjProp varchar(100)
DECLARE @ExcelObject int, @RetCode int, @Document int , @Filename varchar(255)
EXEC @RetCode = sp_OACreate 'Excel.Application', @ExcelObject OUTPUT, 4
IF @RetCode <> 0 or @@Error <> 0 GOTO OLE_Error_Handler
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Workbooks.Open', @Document OUTPUT,'C:\SUCCESS.xls'
-- in case you are creating new Excel file change the upper row with the following
--EXEC @RetCode = sp_OAMethod @ExcelObject, 'Workbooks.Add', @Document OUTPUT
IF @RetCode <> 0 or @@Error <> 0 GOTO OLE_Error_Handler
SELECT @Filename = 'C:\SUCCESS.xls'
DECLARE authors_cursor CURSOR FOR
SELECT MsgSubject FROM mails_header
ORDER BY MsgSubject ASC
OPEN authors_cursor
-- Perform the first fetch.
FETCH NEXT FROM authors_cursor
-- Check @@FETCH_STATUS to see if there
-- are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previ
-- ous fetch succeeds.
SET @Counter=@Counter+1
SET @ObjProp='Application.ActiveWorkbook.Sheets(1).Cells(' + cast(@Counter as varchar(2)) + ', 1).value'
-- genaration OF correct property addressing
EXEC @RetCode = sp_OASetProperty @ExcelObject, @ObjProp, @ExlValue
-- SET the value OF the property
FETCH NEXT FROM authors_cursor INTO @ExlValue
--IF @RetCode <> 0 OR @@Error <> 0 GOTO OLE_Error_Handler
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Application.ActiveWorkbook.Save',Null
-- in case you are creating new Excel file change the upper row with the following
--EXEC @RetCode = sp_OAMethod @ExcelObject,'Application.ActiveWorkbook.SaveAs',Null, 'c:\success.xls'
IF @RetCode <> 0 or @@Error <> 0 GOTO OLE_Error_Handler
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Quit'
-- I've commented the following line deliberately. SEE NOTE AT THE END
--EXEC sp_OADestroy @ExcelObject
GOTO Done
OLE_Error_Handler:
CLOSE authors_cursor
DEALLOCATE authors_cursor
EXEC sp_oageterrorinfo @ExcelObject, @RetCode out, @desc out
-- the last parameter gives small textua
-- l description of the error. It was very
-- usefull!!
SELECT @retCode, @desc
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Quit'
EXEC sp_OADestroy @ExcelObject
GOTO Done
Done:
-- I've commented the last line delibera
-- tely: It was causing the whole automatio
-- n process in SQL to stop
-- so no futher automation instances cou
-- ld be created until the server was resta
-- rted. I read in MSDN that the instance
-- should be destroyed right after the S
-- QL batch is completed, that is why I con
-- sider the last row not neccessary. BUT
-- you should check it yourself
--EXEC sp_OAStop
If you manage to get the code running, please give me a feedback.
A third approach might be to write to a *.csv file
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello!
Thanks for the response 🙂
I would prefer not to use excel automation inside an SQL task (which I am not sure is working as our server lacks Excel installed). I will of course try this approach if there is nothing like using a Global DTS variable in the scripting of the Excel 'table' (Sheet) and the connection opening.
I will keep you posted of any development. Thank you again!
/Hans
-
Hi Hans,
quote:
I would prefer not to use excel automation inside an SQL task (which I am not sure is working as our server lacks Excel installed).that is also my problem.
Good luck
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Hans,
quote:
I would prefer not to use excel automation inside an SQL task (which I am not sure is working as our server lacks Excel installed).a cup of coffee and some thoughts later I believe you don't need to have Excel installed. It should be enough to register the Excel9.olb file on the SQL Server via regsvr32.exe to have access to Excel's Object Library.
What I don't know is whether the Excel9.olb is redistributable, meaning could be you need an Excel license to place to olb on the Server.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I do something similar although the task is not automated in sql but called through vb. What i did was created my dts package to import dat from an excel spread sheet in sql but saved it as activex script. in the script you wil see the filename wich you can set to variable then run your query to set that variable. although i donot schedule the activeX script in sql i am sure you can.
-
Thanks for all the pointers!
I would prefer to do everything in the DTS and not having to call from VB everytime I run it. If a script is the way to go it would solve my problems.
It seems to me like there are two a little bit different problems now.
Problem 1: How to set the Connection to my (uncreated) excelfile.
Problem 2: How to set the destination source to my sheet that I want to create.
Problem 1 seems like it might be solvable with an ActiveX script but since I am not a ActiveX scripting person I am still working on it.
Problem 2 in this light seems to be associated with the first one with one exception: I have to create my table (sheet) first. Creating a sheet is easy by letting an sql task create it. The problem is that in an sql task on an excel source I cannot use the '?' parameter substitution since dynamic sql seems to be out of the question.
So going back to the start maybe even it is creatable by an ActiveX script (without automation, maybe some form of Ad-Hoc query?).
Can anyone please point me to the (to me seemingly) three ActiveX script commands that can be used and how? DTSConnection("x").DataSource (?) DTSConnection("x").ExecuteSQL (?) and DTSTask_DTSDataPumpTask_1.DestinationObjectName (?)
I am going to continue on this soon, but since workday here in Italy ends around now, the need for rest increases 😛
Thanks for all the help and Goodnight America, Wherever you are. (Night Hawk(TM))
-
I do similar things in dts
I would have the datapump task write a batch file and then execute the batch file to rename the file or files.
So write your excel sheet to a generic filename and then from a sql server connection to a textfile destination use the datapump task.
The datapump source is a select statement similar to the following
Select 'ren generic.xls ' + special + '.xls' txt
from table
The txt is just an alias that dts seems to require for the source to destination transformation.
The destination text file has 1 field as well named txt.
The last thing to do is to execute the batch file with the xp_cmdshell task.
-
Hi,
The few times that I have had to work on this, the best approach that I have had was to dump the query to a text file and give it a csv extension. When the user opens it, it automatically opens in excel. I usually drive the package off a view, so when (not if) the export requirements change I just tweak the view. As per the re-creating of the file, if you use a txt/csv file sql SHOULD (??) automatically re-create the file. Hope that helps.
Tom
-
I just finished a similar DTS package. I took the approach of using the current time to generate a unique name for my Excel file... something like ..\Inventory200305091629.xls
The path is stored in a DTS global variable. Add an ActiveX Script Task to your package with the following script. It sets the destination filename for the Excel output. The 'padding' routines just make sure that I get a 'yyyymmddHHMM' format.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oConn, sFilename, sPath
sPath = DTSGlobalVariables("gstrExportPath").Value
sFilename = sPath & "Inventory" & Right("20" & Year( now() ) , 4)
sFilename = sFilename & Right("0" & Month( now() ), 2)
sFilename = sFilename & Right("0" & Day( now() ), 2)
sFilename = sFilename & Right("0" & Hour( now() ) , 2)
sFilename = sFilename & Right("0" & Minute( now() ) , 2 )
sFilename = sFilename & ".xls"
Set oConn = DTSGlobalVariables.Parent.Connections("Connection 2")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
Good luck!
-
Ted Langewisch: Thanks a bunch! That seems to get me exactly where I want! Now I am only down to Creating my Table (sheet) at the destination. And if VB Scripting was not such a pain in the a__ when it comes to debugging I would be happier.
Thanx again!
Ciao from Hans!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply