DTS/ActiveX help to rename an excel file to include current date

Question

Post reply

DTS/ActiveX help to rename an excel file to include current date

Paula-129590

SSC-Addicted

Points: 437
More actions
December 5, 2007 at 9:58 am

#76838

Hi,
I am using a DTS package to extract data from a table and export it to an excel file. This task needs to run on a weekly basis and the filename should contain the date the file was created. I have sucessfully used the activex scropt below to rename .txt files but when I try to use it for Excel files it always defaults to the default filename I specified in the destination file properties. Can anyone show me how to do this for Excel files?
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
Dim sFilename, oPkg, oConn
Dim sYear, sMonth, sDay
If Month(Now) > 10 Then sMonth = Month(Now) Else sMonth = "0" & Month(Now)
If Day(Now) > 10 Then sDay = Day(Now) Else sDay = "0" & Day(Now)
sFilename = "\\servername\D$\Daily Reports\Flagcodes " & sDay & sMonth & Year(Now) & ".xls"
Set oPkg = DTSGlobalVariables.Parent
Set oConn = oPkg.Connections(2)
oConn.DataSource = sFileName
Set oConn = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Thanks for your help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Norman Kelm SSCommitted Points: 1922 More actions · Answer 1

Hi Paula,

Seems like what you are doing should work, but I've not done it that way.

What I have done in the past is to set a global variable to the new file name in the script. Then add a Dynamic Properties Task after the ActiveX Task and set the Connection DataSource property with the global variable value.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

teddy.sincire SSC Veteran Points: 218 More actions · Answer 2

Hi nite_eagle,

I'm stumped. I'm trying to do the same thing as Paula, but I get an error "The Microsoft Jet engine doesn't recognize object 'New Table'..."

Do you know what I'm doing wrong?

Teddy

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I had to do this. The code was taken from me. You can use the Dynamic Properties task but please be aware there are other options.

Unfortunately I can't remember the exact deteils but I created numberous packages in DTS and some in SSIS to do this.

1.) I created a global variables (Source Path FileName Prefix, MMDDYYYY, file Extension, Destination Directory & File Name, Archicve Directory, etc.

2.) I add an ActiveX Script Task wherein I assign a value to the global variable

3.) I add a Dynamic Property Task wherein I assign the datasource to the global variable

4.) Then I placed a workflow.

I hope this helps.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

teddy.sincire SSC Veteran Points: 218 More actions · Answer 4

Thanks Welsh! You did help. I finally got it to work.

Teddy