|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Wednesday, January 26, 2011 5:45 AM
Points: 229,
Visits: 134
|
|
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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, November 27, 2011 4:02 PM
Points: 130,
Visits: 318
|
|
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/
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 02, 2012 2:28 PM
Points: 114,
Visits: 113
|
|
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
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 2,684,
Visits: 3,031
|
|
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/
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 02, 2012 2:28 PM
Points: 114,
Visits: 113
|
|
Thanks Welsh! You did help. I finally got it to work.
Teddy
|
|
|
|
