September 5, 2002 at 6:36 am
What am I doing wrong. The code below runs a DTS package which outputs data from sql server 2000 to an excel spreadsheet.
I have to views that I want to output however only 1 outputs and they are put in separate files rather than in the same
excel spreadsheet (which i am defining in the constants.
Example 2 views. I have made a dts package that is to output to and excel file. All I want to do is have this code change the
source name in each of the 2 source files, which are located in a dts packaged together and then output 1 view to a worksheet
and outoput the other view to a worksheet of its own all in the same spreadheet.
When the package is run manually and the output source entered manually I get the final result.
and excel file (i.e. text.xls) and 2 worksheets inside the excel file that represent each of the views.
What do I need to do to have this code just change the output file and get the package to dump the data into a newly named file.
I probably confused you so please hit me with questions.
Thanks in advance.
CODE:
***********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
'**********************************************************************
' Public Constants
'************************************************************************
Const sFilesOutboundDirectory = "\\ss-046\ServicePaks\test.xls"
Const sFilesOutboundDirectory1 = "\\ss-046\ServicePaks\test.xls"
'**********************************************************************
' Main
'************************************************************************
Function Main()
Dim oExportDSDataConnection, oCommand, oDTSPackage, vExcelFile, bPackageExecutionInfo
Dim oExportDSDataConnection1, oCommand1, oDTSPackage1, vExcelFile1, bPackageExecutionInfo1
Set oExportDSDataConnection = CreateObject("ADODB.Connection")
oExportDSDataConnection.provider = "SQLOLEDB"
oExportDSDataConnection.open "DB1", "ds_export", "export"
oExportDSDataConnection.defaultdatabase = "DS"
Set oExportDSDataConnection1 = CreateObject("ADODB.Connection")
oExportDSDataConnection1.provider = "SQLOLEDB"
oExportDSDataConnection1.open "DB1", "ds_export", "export"
oExportDSDataConnection1.defaultdatabase = "DS"
Set oCommand = CreateObject("ADODB.Command")
Set oCommand.ActiveConnection = oExportDSDataConnection
oCommand.CommandType = 4
Set oCommand1 = CreateObject("ADODB.Command")
Set oCommand1.ActiveConnection = oExportDSDataConnection
oCommand1.CommandType = 4
' Set package output folder and name
Set oDTSPackage = CreateObject("DTS.Package")
oDTSPackage.LoadFromSQLServer "DB1", "ds_export", "export",,,,,"DTS_Weekly_Datasource_Export_to_Excel_Test_Aux"
Set oDTSPackage1 = CreateObject("DTS.Package")
oDTSPackage1.LoadFromSQLServer "DB1", "ds_export", "export",,,,,"DTS_Weekly_Datasource_Export_to_Excel_Test_Evt"
vExcelFile = sFilesOutboundDirectory
vExcelFile1 = sFilesOutboundDirectory1
oDTSPackage.Connections(2).DataSource = vExcelFile
oDTSPackage.Execute
oDTSPackage1.Connections(2).DataSource = vExcelFile1
oDTSPackage1.Execute
oDTSPackage.Uninitialize
Set oDTSPackage = nothing
oDTSPackage1.Uninitialize
Set oDTSPackage1 = nothing
End Function
September 8, 2002 at 12:17 pm
Have you tried using a XLS data source and transformations to it from your SQL Server. It's not ActiveScript but it is more DTS intuitive.
I usually proof my scripting in ASP pages or in VBA and then alter for DTS ActiveScript, but usually as a last resort to a SQL solutions.
I have also had success setting up template spreadsheets with Data Queries and Pivots that are called from DTS Script to refresh them selves. Then I copy out the file for end users.
ActiveWorkbook.Refreshall
I don't have experience with the path you are looking at.
Regards,
Neal
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply