January 9, 2003 at 4:22 pm
Hi all, need to know if there is a way of creating an Excel file and dumping the results of the query in that file. Also, the name of the file changes every day, the file naming convention is filename_mm_dd(month and day). Can someone help me with this please.
Thank you
January 9, 2003 at 4:23 pm
DTS
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 9, 2003 at 4:28 pm
How can I change the file name every day that I run these packages?
Thanks
January 10, 2003 at 9:40 am
K, create a package with a sql datasource, and and excel file connection(name the excel connection ExcelOutPut). Create a transform betweeen the two (outputting to the excel file). Test this to make sure it creates the excel file. Add an ActiveX script task. Add a workflow between the excel connection and the activex script task for on success. Then paste the below code into the activeX script task, deleting all the old code. This was the easiest way I found to do this, and that way of someone changes the output filename in your excel connection then the script will still work as long as the connection is named ExcelOutput
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
CONST FILENAME_EXT = ".xls"
Function GetDateTimeForFileName(InCludeTime)
GetDateTimeForFileName = Year(Date) & _
Month(Date) & _
Day(Date)
If InCludeTime = True Then
GetDateTimeForFileName = GetDateTimeForFileName & "_" & _
Hour(Time) & _
Minute(Time) & _
Second(Time)
End If
End Function
Function Main()
Dim objPkg
Dim objConn
Dim objFSO
Dim strOrigFileName
Dim strFileName
Set objPkg = DTSGlobalVariables.parent
'Get a reference to the Excel output connection
Set objConn = objPkg.Connections("ExcelOutput")
Set objFSO = CreateObject("Scripting.FilesystemObject")
'ensure the datasource has the .xls extension on it as it may not
strOrigFileName = Replace(objConn.Datasource, FILENAME_EXT, "") & FILENAME_EXT
'set the new file name
strFileName = Replace(objConn.Datasource, FILENAME_EXT, "") & GetDateTimeForFileName(True) & FILENAME_EXT
'rename as the new file
objFSO.Copyfile strOrigFileName, strFileName, True
'delete the old file
objFSO.DeleteFile strOrigFileName, True
Main = DTSTaskExecResult_Success
End Function
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Edited by - tcartwright on 01/10/2003 09:45:35 AM
Tim C //Will code for food
January 10, 2003 at 9:43 am
If you want I can script the DTS package I created so that you can see it work. I just did not do that as it would be rather large.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 10, 2003 at 10:23 am
Thank you very much. I will try it and let you know.
January 10, 2003 at 10:27 am
Well I have already found a bug. It runs fine the first time, but the second time you run it the transformation has mysteriously cleared itself. I am unsure of why this is happening and I am looking at it now. Does anyone out there know why this could possibly happen?
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 13, 2003 at 11:58 am
A simple way is to write a VBScript to do it. Use ADO oject to query the DB and use the Excel VBA objects, methods to paste the results. For example.
.Sheets("Sheet1").Range("A1").CopyFromRecordset rsData
January 16, 2003 at 2:04 am
You could also approach if from the other end. Use MS Query in Excel and past your SQL into the MS Query SQL window. You can put a button on the spreadsheet that runs a macro that dynamically requeries the data or have a startup macro that runs and requeries every time the spreadsheet is opened.
G. Milner
January 16, 2003 at 6:09 am
Just a quick question for Excel user. Out of curiosity, I did test the export to excel via DTS with sample data. One of the fields has a bunch of records with leading zeros, which I lost when the export finished. I know there is way to keep the leading zeros, but I haven't use Excel in a verryyyy long time and I don't remeber how it is done.
Is just a question out of curiosity, is not that I am using it or anything like that. I usually read the postings and test the solution on my own, I am always trying to learn more.
Thank you.
January 16, 2003 at 8:19 am
To keep the leading zeros in Excel or keep the original format of the data (e.g., DEC1 will, by default change to December 1st) you can either format the column as text (Format/Cells Number tab) or you can precede the outputted value with an apostrophe (e.g., '0001).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply