ForceQuery: Boolean;
ForceQuery: boolean;
The ForceQuery property determines whether a file for data source on ETL task execution is requested.
Available values:
True. A file for data source is requested on ETL task execution.
NOTE. If an ETL task is created via data import, this property is set to True.
False. Default. A file for data source is not requested on ETL task execution.
Executing the example requires:
An ETL task with the ETL identifier in the repository.
A XLSX file named PROV.xls contained in the root of the C disc. This file contains a sheet named Sheet1.
A XLS file named CONS.xls contained in the root of the C disc. This file contains a sheet named Sheet1.
Add links to the Andy, Drawing, Dt, Etl, Metabase system assemblies.
Sub UserProc;
Var
MB: IMetabase;
EtlTask: IEtlTask;
i, j, k: Integer;
EtlProvider: IEtlPlainDataProvider;
ETLConsumer: IEtlPlainDataConsumer;
QueryForFile: IEtlPlainDataForceQueryForFile;
dtProv: IDtExcelProviderEx;
dtCons: IDtExcelConsumer;
WxProvider, WxConsumer: IWxRectangle;
WxETLProvider, WxETLConsumer: IWxETLObject;
ProvPlainOutput: IEtlPlainOutput;
ConsPlainInput: IEtlPlainInput;
PlainLink: IEtlPlainLink;
WLink: IWxLink;
WxETLLink: IWxETLObject;
FieldMapping: IEtlPlainLinkFieldMapping;
ETLSrcF, ETLDestF: IEtlPlainField;
Begin
// Get repository
MB := MetabaseClass.Active;
// Get ETL task
ETLTask := MB.ItemById("ETL").Edit As IEtlTask;
// Remove ETL task objects
If EtlTask.Workspace.Shapes.Count > 0 Then
j := EtlTask.Workspace.Shapes.Count - 1;
For i := j To 0 Step - 1 Do
EtlTask.Workspace.Shapes.Item(i).Delete;
End For;
End If;
If EtlTask.Count > 0 Then
k := EtlTask.Count - 1;
For i := k To 0 Step - 1 Do
EtlTask.Item(i).Remove;
End For;
End If;
// Create EXCEL provider
ETLProvider := ETLTask.Create(EtlObjectType.PlainDataExcelProviderEx) As IEtlPlainDataProvider;
ETLProvider := ETLProvider.Edit;
ETLProvider.Id := "Excel_Provider";
ETLProvider.Name := "Excel_Provider";
dtProv := ETLProvider.Provider As IDtExcelProviderEx;
dtProv.File := "C:\Prov.xlsx";
dtProv.Format := "xlsx";
dtProv.Sheet := "Sheet1";
dtProv.FieldsFromFile;
// Enable file request on ETL task execution
QueryForFile := ETLProvider As IEtlPlainDataForceQueryForFile;
QueryForFile.ForceQuery := True;
// Fill in fields from provider
ETLProvider.FillFromSource;
// Save provider
ETLProvider.Save;
// Create EXCEL consumer
ETLConsumer := ETLTask.Create(EtlObjectType.PlainDataExcelConsumer) As IEtlPlainDataConsumer;
ETLConsumer := ETLConsumer.Edit;
ETLConsumer.Id := "Excel_Consumer";
ETLConsumer.Name := "Excel_Consumer";
dtCons := ETLConsumer.Consumer As IDtExcelConsumer;
dtCons.File := "C:\Cons.xls";
dtCons.Table := "Sheet1";
// Create a visual provider view
WxProvider := EtlTask.Workspace.CreateRectangle;
WxETLProvider := New WxETLObject.Create;
WxETLProvider.ETLObject := EtlProvider;
WxProvider.Style.TextPosition := WxTextPosition.Bottom;
WxProvider.Style.PictureMarginTop := -10;
WxProvider.PinPosition := New GxPointF.Create(0, 0);
WxProvider.Extension := WxETLProvider As IWxShapeExtension;
// Create a visual consumer representation
WxConsumer := EtlTask.Workspace.CreateRectangle;
WxETLConsumer := New WxETLObject.Create;
WxETLConsumer.ETLObject := EtlConsumer;
WxConsumer.Style.TextPosition := WxTextPosition.Bottom;
WxConsumer.Style.PictureMarginTop := -10;
WxConsumer.PinPosition := New GxPointF.Create(50, 0);
WxConsumer.Extension := WxETLConsumer As IWxShapeExtension;
// Create a link and its visual representation
ProvPlainOutput := EtlProvider.PlainOutput;
ConsPlainInput := EtlConsumer.PlainInput;
PlainLink := EtlTask.CreatePlainLink;
PlainLink.DestinationObjectInput := ConsPlainInput;
PlainLink.SourceObjectOutput := ProvPlainOutput;
ConsPlainInput.Fields.Fill(ProvPlainOutput.Fields);
// Link provider and consumer fields
For i := 0 To EtlProvider.PlainOutput.Fields.Count - 1 Do
ETLSrcF := EtlConsumer.PlainInput.Fields.Item(i);
ETLDestF := EtlProvider.PlainOutput.Fields.Item(i);
FieldMapping := PlainLink.Link(EtlConsumer.PlainInput.Fields.Item(i));
FieldMapping.Type := EtlPlainLinkFieldMappingType.Field;
FieldMapping.Field := EtlProvider.PlainOutput.Fields.Item(i);
End For;
WLink := EtlTask.Workspace.AutoLinkShapes(WxProvider, WxConsumer);
WLink.Style.LinePenBeginWxCap := WxLineCap.Flat;
WLink.Style.LinePenEndWxCap := WxLineCap.Arrow30DegreeFilled;
WxETLLink := New WxEtlObject.Create;
WxETLLink.EtlObject := PlainLink;
WLink.Extension := WxETLLink As IWxShapeExtension;
// Save consumer
ETLConsumer.Save;
// Save ETL task
(ETLTask As IMetabaseObject).Save;
End Sub UserProc;
After executing the example the EXCEL Provider, EXCEL Consumer and link between them are created in the ETL task. If the file is not specified as a data source on the Import Settings page, the file is requested.
The requirements and result of the Fore.NET example execution match with those in the Fore example.
Imports Prognoz.Platform.Interop.Andy;
Imports Prognoz.Platform.Interop.Drawing;
Imports Prognoz.Platform.Interop.Dt;
Imports Prognoz.Platform.Interop.Etl;
…
Public Shared Sub Main(Params: StartParams);
Var
MB: IMetabase;
EtlTask: IEtlTask;
i, j, k: Integer;
EtlProvider: IEtlPlainDataProvider;
ETLConsumer: IEtlPlainDataConsumer;
QueryForFile: IEtlPlainDataForceQueryForFile;
dtProv: IDtExcelProviderEx;
dtCons: IDtExcelConsumer;
WxProvider, WxConsumer: IWxRectangle;
WxETLProvider, WxETLConsumer: IWxETLObject;
ProvPlainOutput: IEtlPlainOutput;
ConsPlainInput: IEtlPlainInput;
PlainLink: IEtlPlainLink;
WLink: IWxLink;
WxETLLink: IWxETLObject;
FieldMapping: IEtlPlainLinkFieldMapping;
ETLSrcF, ETLDestF: IEtlPlainField;
PointF: GxPointF = New GxPointFClass();
Begin
// Get repository
MB := Params.Metabase;
// Get ETL task
ETLTask := MB.ItemById["ETL"].Edit() As IEtlTask;
// Remove ETL task objects
If EtlTask.Workspace.Shapes.Count > 0 Then
j := EtlTask.Workspace.Shapes.Count - 1;
For i := j To 0 Step - 1 Do
EtlTask.Workspace.Shapes.Item[i].Delete();
End For;
End If;
If EtlTask.Count > 0 Then
k := EtlTask.Count - 1;
For i := k To 0 Step - 1 Do
EtlTask.Item[i].Remove();
End For;
End If;
// Create EXCEL provider
ETLProvider := ETLTask.Create(EtlObjectType.eotPlainDataExcelProviderEx) As IEtlPlainDataProvider;
ETLProvider := ETLProvider.Edit();
ETLProvider.Id := "Excel_Provider";
ETLProvider.Name := "Excel_Provider";
dtProv := ETLProvider.Provider As IDtExcelProviderEx;
dtProv.File := "C:\\PROV.xlsx";
dtProv.Format := "xlsx";
dtProv.Sheet := "Sheet1";
dtProv.FieldsFromFile();
// Enable file request on ETL task execution
QueryForFile := ETLProvider As IEtlPlainDataForceQueryForFile;
QueryForFile.ForceQuery := True;
// Fill in fields from provider
ETLProvider.FillFromSource();
// Save provider
ETLProvider.Save();
// Create EXCEL consumer
ETLConsumer := ETLTask.Create(EtlObjectType.eotPlainDataExcelConsumer) As IEtlPlainDataConsumer;
ETLConsumer := ETLConsumer.Edit();
ETLConsumer.Id := "Excel_Consumer";
ETLConsumer.Name := "Excel_Consumer";
dtCons := ETLConsumer.Consumer As IDtExcelConsumer;
dtCons.File := "C:\\CONS.xls";
dtCons.Table := "Sheet1";
// Create a visual provider view
WxProvider := EtlTask.Workspace.CreateRectangle();
WxETLProvider := New WxETLObject.Create();
WxETLProvider.ETLObject := EtlProvider;
WxProvider.Style.TextPosition := WxTextPosition.wtpBottom;
WxProvider.Style.PictureMarginTop := -10;
PointF.Create(0, 0);
WxProvider.PinPosition := PointF;
WxProvider.Extension := WxETLProvider As IWxShapeExtension;
// Create a visual consumer view
WxConsumer := EtlTask.Workspace.CreateRectangle();
WxETLConsumer := New WxETLObject.Create();
WxETLConsumer.ETLObject := EtlConsumer;
WxConsumer.Style.TextPosition := WxTextPosition.wtpBottom;
WxConsumer.Style.PictureMarginTop := -10;
PointF.Create(50, 0);
WxConsumer.PinPosition := PointF;
WxConsumer.Extension := WxETLConsumer As IWxShapeExtension;
// Create a link and its visual view
ProvPlainOutput := EtlProvider.PlainOutput;
ConsPlainInput := EtlConsumer.PlainInput;
PlainLink := EtlTask.CreatePlainLink();
PlainLink.DestinationObjectInput := ConsPlainInput;
PlainLink.SourceObjectOutput := ProvPlainOutput;
ConsPlainInput.Fields.Fill(ProvPlainOutput.Fields);
// Link provider and consumer fields
For i := 0 To EtlProvider.PlainOutput.Fields.Count - 1 Do
ETLSrcF := EtlConsumer.PlainInput.Fields.Item[i];
ETLDestF := EtlProvider.PlainOutput.Fields.Item[i];
FieldMapping := PlainLink.Link[EtlConsumer.PlainInput.Fields.Item[i]];
FieldMapping.Type := EtlPlainLinkFieldMappingType.fmtField;
FieldMapping.Field := EtlProvider.PlainOutput.Fields.Item[i];
End For;
WLink := EtlTask.Workspace.AutoLinkShapes(WxProvider, WxConsumer);
WLink.Style.LinePenBeginWxCap := WxLineCap.wlcFlat;
WLink.Style.LinePenEndWxCap := WxLineCap.wlcArrow30DegreeFilled;
WxETLLink := New WxEtlObject.Create();
WxETLLink.EtlObject := PlainLink;
WLink.Extension := WxETLLink As IWxShapeExtension;
// Save consumer
ETLConsumer.Save();
// Save ETL task
(ETLTask As IMetabaseObject).Save();
End Sub;
See also: