如何使用SQL表作为源填充BIML中的数组

Question

我有一个包含文件名和其他一些属性的表。我希望将这些记录插入到BIML中的数组中，以便循环遍历这些文件(并使用附加属性导入它们)。

导入这样一个文件的代码大部分已经完成。现在只需要一个部分，我把下面的信息放在一个数组中，这样我就可以遍历它们。

 CREATE TABLE [config].[FilesToImport](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [path] [nvarchar](1024) NULL,
    [importfilename] [nvarchar](1024) NULL,
    [dest_server] [nvarchar](256) NULL,
    [dest_db] [nvarchar](256) NULL,
    [dest_schema] [nvarchar](256) NULL,
    [refreshtype] [int] NULL,
    [separator] [nvarchar](5) NULL,
    [order] [int] NULL
) 
GO
SET IDENTITY_INSERT [config].[FilesToImport] ON 
GO
INSERT [config].[FilesToImport] ([id], [path], [importfilename], [dest_server], [dest_db], [dest_schema], [refreshtype], [separator],  [order]) VALUES (1, N'c:\temp', N'FileA.csv', N'SMVPDA001', N'TEST', N'landingzone', 0, N';',  1)
GO
INSERT [config].[FilesToImport] ([id], [path], [importfilename], [dest_server], [dest_db], [dest_schema], [refreshtype], [separator], [order]) VALUES (2, N'c:\temp', N'FileZ.csv', N'SMVPDA001', N'TEST', N'landingzone', 0, N';', 2)
GO
INSERT [config].[FilesToImport] ([id], [path], [importfilename], [dest_server], [dest_db], [dest_schema], [refreshtype], [separator],  [order]) VALUES (3, N'c:\temp', N'File4.cvs', N'SMVPDA001', N'TEST', N'landingzone', 0, N';', 3)
GO
SET IDENTITY_INSERT [config].[FilesToImport] OFF

因此，下面的代码基本上应该是上面提到的表中的文件名。

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <#
    string Applicatie = "BIML";
    string Prefix = "import";
     
    string fileName;
    string path = @"c:\temp";
    string[] myFiles = Directory.GetFiles(path, "*.csv");
     
    string[] myColumns;
    #>
    <FileFormats>
    <#
    foreach (string filePath in myFiles)
    {
 
    #>
    <FlatFileFormat Name="FlatFileFormat<#=Path.GetFileNameWithoutExtension(filePath)#>" RowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="false">
        <Columns>
            <# 
                 
                StreamReader myFile = new StreamReader(filePath);
                myColumns = myFile.ReadLine().Replace("\"","").Split('|');
                 myFile.Close();
                 
                // to determine the column delimeter 
                int columnCount = 0;
                string columnDelimiter = "";
                //WriteLine($"<!-- ref count {myColumns.Count} -->");
                    foreach(string myColumn in myColumns)
                    {
                    
                        columnCount++;
                        bool finalColumn = columnCount == myColumns.Length;
                        WriteLine($"<!-- actual count {columnCount} -->");
                      //  WriteLine($"<!-- what {columnCount == myColumns.Count} {finalColumn} -->");
                        
                        if (finalColumn)
                        {
                            columnDelimiter = "CRLF";
                        }
                        else
                        {
                            columnDelimiter = "|";
                        }
                        WriteLine($"<!-- delimiter {columnDelimiter} -->");
                #>
                <Column Name="<#=myColumn#>" DataType = "String" Length="250" Delimiter="<#=columnDelimiter#>"></Column>
                <# } #>
            </Columns>
        </FlatFileFormat>
            <#}#>
    </FileFormats>
    <Connections>
            <#
            foreach (string filePath in myFiles)
            {
             
            #>
            <FlatFileConnection Name="FF_CSV-<#=Path.GetFileNameWithoutExtension(filePath)#>" 
                                FilePath="<#=filePath#>" 
                                FileFormat="FlatFileFormat<#=Path.GetFileNameWithoutExtension(filePath)#>" 
             >
                 <Expressions>
                    <Expression ExternalProperty="TextQualifier">"\""</Expression>
                    <Expression ExternalProperty="RowDelimiter">"CRLF"</Expression>
                </Expressions>

            </FlatFileConnection>
            <# } #>
            <OleDbConnection
                Name="STG_<#=Applicatie#>" 
                ConnectionString="Data Source=SQLSERVER;Initial Catalog=TEST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
    </Connections> 
        <Packages>
 
    <#       // Loop trough the files
            int TableCount = 0;
            foreach (string filePath in myFiles)
            { 
                TableCount++;
                fileName = Path.GetFileNameWithoutExtension(filePath);
                #>
                <Package Name="stg_<#=Prefix#>_<#=TableCount.ToString()#>_<#=fileName#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="secret">
                    <Variables>
                        <Variable Name="CountStage" DataType="Int32" Namespace="User">0</Variable>
                    </Variables>               
                    <Tasks>
                        <ExecuteSQL ConnectionName="STG_<#=Applicatie#>" Name="SQL-Truncate <#=fileName#>">
                            <DirectInput>TRUNCATE TABLE dbo.<#=Prefix#>_<#=fileName#></DirectInput>
                        </ExecuteSQL>
                         
                        <Dataflow Name="DFT-Transport CSV_<#=fileName#>">
                            <Transformations>
                                <FlatFileSource Name="SRC_FF-<#=fileName#> " ConnectionName="FF_CSV-<#=Path.GetFileNameWithoutExtension(filePath)#>">
                                </FlatFileSource>
                                 
                                <OleDbDestination ConnectionName="STG_<#=Applicatie#>" Name="OLE_DST-<#=fileName#>" >
                                <ExternalTableOutput Table="dbo.<#=Prefix#>_<#=fileName#>"/>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Package>
    <#    }    #>
 
                <!-- Create Master Package -->
                <Package Name="stg_<#=Prefix#>_0_Master" ConstraintMode="Parallel" AutoCreateConfigurationsType="None" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="secret">
                    <Tasks>
                    <#  int TableCount2 = 0;
                        foreach (string filePath in myFiles)
                        { 
                                TableCount2++;
                                fileName = Path.GetFileNameWithoutExtension(filePath); #>
                 
                            <ExecutePackage Name="stg_<#=Prefix#>_<#=TableCount2.ToString()#>_<#=fileName#>">
                                <ExternalProjectPackage  Package="stg_<#=Prefix#>_<#=TableCount2.ToString()#>_<#=fileName#>.dtsx" />
                            </ExecutePackage>
                        <#
                        }
                        #>    
                    </Tasks>
                </Package>
 
    </Packages>
</Biml>
 
<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.IO"#>

Answer 1

我看到您用实际的代码编辑了它，但问题的关键是您正在寻找ExternalDataAccess.GetDataTable方法。这里还提到了BIML中的Foreach数据低滤波器，奇怪的是我没有回答.

该方法填充数据表，然后将其作为二维数组处理。Rows属性允许您枚举行，然后每个DataRow都是一个可为空的对象数组。是的，即使它在数据库中有一个类型，您也必须显式地转换为其他类型来处理数据。

<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
// Could also pull this from this.RootNodes.Connections
string connectionStringSource = @"Server=.\dev2017;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11.1;";
string query = @"SELECT
    FTI.id
,   FTI.path
,   FTI.importfilename
,   FTI.dest_server
,   FTI.dest_db
,   FTI.dest_schema
,   FTI.refreshtype
,   FTI.separator
,   FTI.defaultfieldtype
,   FTI.[order]
FROM
    config.FilesToImport AS FTI;";

DataTable dt = null;
dt = ExternalDataAccess.GetDataTable(connectionStringSource, query);

foreach(DataRow row in dt.Rows)
{
    // Downside to data table is everything is object so explict casts required
    WriteLine($"<!-- {row[0]},{row[1]},{row[2]} etc -->");
    
}
#>
    <FileFormats>
<#
// Enumerate through the data table to define FFCM 
foreach(DataRow row in dt.Rows)
{
#>
        <FlatFileFormat Name="SO_<#=row[0]#>" RowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="false"/>
<#
}
#>    
    </FileFormats>
    <Packages>
<#
// Enumerate through the data table to define packages, etc
foreach(DataRow row in dt.Rows)
{
#>
    <Package Name="Load_<#=row[5]#>_<#=row[0]#>" />
<#
}
#>    
    </Packages>
</Biml>

浏览一下您的代码，您可能会对CallBimlScript或CallBimlScriptWithOutput (我博客上的示例)感兴趣。而不是单块BimlScript，您可以将代码分解为函数(本质上)。在复制-o-马季奇文章中，我传递一个模式和一个表，构建包的单个实例的所有逻辑都在其中，所以主构建器包只关注构建顶级构件--连接、包等，并将工作推送到函数中。

GetBimlScriptWithOutput的聪明之处在于您可以将数据返回给调用者。例如，当您创建包时，您正在构建您的包名，并且您已经为您的主包重复了执行它们的逻辑。不要重复你自己。相反，让包生成器返回一个属性，即新包的名称(并添加到数组/列表中)。然后，Orchestrator包可以简单地获取包名，而不知道它们是如何派生的。

• https://billfellows.blogspot.com/2015/01/biml-replicate-o-matic.html
• https://billfellows.blogspot.com/2018/10/biml-excel-data-source-without-excel.html