Существует пакет dtsx, который запускает отчеты SSRS с использованием таблицы конфигурации, а затем выполняет макрос, где это необходимо, в созданном файле .xls (это сервер 2008 R2).
Каждая строка таблицы конфигураций содержит сведения об отчете, который необходимо запустить, сведения о макросе и список параметров и значений в формате xml, которые необходимо передать макросу; в пакете есть цикл foreach, который запускает отчет, добавляет макрос в выходной файл, запускает макрос и движется дальше.
Создается впечатление, что он был максимально обобщен, чтобы в таблицу конфигураций можно было вписать любой отчет и любой макрос.
Это работает нормально, но при всем том, что он кажется гибким, фактический C # в коде скрипта, который загружает и запускает макрос, имеет очень жесткую структуру, а это означает, что можно запускать только макросы с ровно 4 параметрами - я хотел бы сделать его более гибким, но я изо всех сил пытаюсь понять, как дать команде ExcelObject.Run()
правильное (переменное) количество параметров в зависимости от количества узлов в конфигурации xml.
У меня нет большого опыта работы с C#, но, кажется, я прочитал эту статью Microsoft и эту статью Stackoverflow, которую я могу создайте массив параметров и передайте это... Я просто не могу понять, как это сделать. Вот существующий скрипт:
using System;
using System.IO;
using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.SqlServer.Dts.Runtime;
using System.Reflection;
using VBIDE = Microsoft.Vbe.Interop;
namespace ST_b2148758d9a44ee4bc0d01a2d900ce9d.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
Variables UserVariables = null;
Dts.VariableDispenser.LockForRead("User::SaveLoc_Root");
Dts.VariableDispenser.LockForRead("User::SaveLoc_SubFolder");
Dts.VariableDispenser.LockForRead("User::SaveLoc_FileName");
Dts.VariableDispenser.LockForRead("User::SaveLoc_FileExtension");
Dts.VariableDispenser.LockForRead("User::VBA_Macro_Name");
Dts.VariableDispenser.LockForRead("User::VBA_Parameters");
Dts.VariableDispenser.LockForRead("User::VBA_Script");
Dts.VariableDispenser.GetVariables(ref UserVariables);
string SaveToLocation = null, ConstantName = null, DateFormat = null , SheetNameInCell = null;
//build the filename of the report we just made.
//@"C:\Temp\Repart.xls";
string Report_File_name = UserVariables["User::SaveLoc_Root"].Value.ToString()
+ UserVariables["User::SaveLoc_SubFolder"].Value.ToString()
+ UserVariables["User::SaveLoc_FileName"].Value.ToString()
+ UserVariables["User::SaveLoc_FileExtension"].Value.ToString();
//The macro and the macro name were stored in the original data set, so we can get those from local variables.
string Macro = UserVariables["User::VBA_Script"].Value.ToString();
string Macro_name = UserVariables["User::VBA_Macro_Name"].Value.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(UserVariables["User::VBA_Parameters"].Value.ToString());
XmlNodeList Parameters = doc.GetElementsByTagName("Parameter");
//skip through all the parameters we might have - if more are added, this will need to be changed.
for (int i = 0; i < Parameters.Count; i++)
{
string ParamName = Parameters[i].Attributes["Name"].Value.ToString();
if (ParamName == "SaveToLocation")
{
SaveToLocation = Parameters[i].Attributes["Value"].Value.ToString();
}
else if (ParamName == "ConstantName")
{
ConstantName = Parameters[i].Attributes["Value"].Value.ToString();
}
else if (ParamName == "DateFormat")
{
DateFormat = Parameters[i].Attributes["Value"].Value.ToString();
}
else if (ParamName == "SheetNameInCell")
{
SheetNameInCell = Parameters[i].Attributes["Value"].Value.ToString();
}
}
//Get Excel ready to be opened
Excel.Application ExcelObject = default(Excel.Application);
Excel.WorkbookClass oBook = default(Excel.WorkbookClass);
Excel.Workbooks oBooks = default(Excel.Workbooks);
//get the vba module ready
VBIDE.VBComponent module = null;
//open excel in the background
ExcelObject = new Excel.Application();
ExcelObject.Visible = false;
//Open our report
oBooks = ExcelObject.Workbooks;
oBook = (Excel.WorkbookClass)oBooks.Open
(Report_File_name
,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value,Missing.Value
,Missing.Value,Missing.Value,Missing.Value,Missing.Value ,Missing.Value,Missing.Value);
//Add a module to our report and populate it with our vba macro
module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(Macro);
//run the macro
ExcelObject.Run
( Macro_name,SaveToLocation,ConstantName,DateFormat,SheetNameInCell, Missing.Value, Missing.Value, Missing.Value,Missing.Value
,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value
,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value
,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value);
ExcelObject.Visible = false;
ExcelObject.UserControl =false;
//oBook.Save();
ExcelObject.DisplayAlerts =false;
ExcelObject.Application.Quit();
ExcelObject =null;
}
}
}
XML-файл выглядит следующим образом (я хотел бы иметь возможность добавлять больше или меньше параметров без сбоя - при этом я не возражаю против максимального количества параметров):
<VBAParameters>
<Notes>The source spreadsheet is produced by the SSIS package "Reports.dtsx" which runs on SQL7. The settings here will be used to split that workbook into several files.
*SaveToLocation is where the script will save the each departments report (each sheet from the source workbook)
*ConstantName is the text that will appear in all files, along with the date and the department name
*DateFormat is the format of the date that will appear in the filename
*SheetNameInCell gives the address of the cell that in each sheet of the source spreadsheet contains the department name.
</Notes>
<Parameters>
<Parameter Name="SaveToLocation" Value="C:\Temp" />
<Parameter Name="ConstantName" Value="Post Summary" />
<Parameter Name="DateFormat" Value="yyyyMM" />
<Parameter Name="SheetNameInCell" Value="A5" />
</Parameters>
</VBAParameters>
Любое руководство о том, как я мог бы передать необходимое количество параметров, отличных от missing.value
, в макрос, будет с благодарностью получено. Таким образом, мои усилия оказались напрасными. К счастью, средство чтения xml возвращает параметры в предсказуемом порядке, поэтому имена не нужны.