在 SSIS 中使用 C# 脚本获取时间戳 CSV 的文件名 [英] Get Filename of Timestamped CSV using C# Script in SSIS

查看：44
本文介绍了在 SSIS 中使用 C# 脚本获取时间戳 CSV 的文件名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在开发一个 SSIS 包，该包将数据从 CSV 文件加载到 SQL Server 的暂存表中.CSV 文件将采用以下格式:

FILENAME_YYYYMMDD_HHMMSS.csv

YYMMDD 部分将始终对应于昨天的日期，但 HHMMSS 部分将根据创建 CSV 文件的确切时间而有所不同.

有没有办法在 SSIS 中使用脚本组件来执行以下操作:

  1. 检查是否有昨天写入的文件(YYYYMMDD = 昨天的日期)
  2. 如果是，将完整的文件名存储到一个变量中

解决方案

我认为这是其他答案的应用

  • YYYYMMDD 模式 可改善您的 BI 生活的免费插件

  • 在 IS 项目中，右键单击该项目并添加一个新的 biml 文件
  • 双击 BimlScript.biml 并添加以下内容替换所有内容
  • 保存
  • 右键单击 BimlScript.biml 文件并选择生成 SSIS 包

利润

<!-- 创建一个基本的平面文件源定义--><文件格式><平面文件格式名称="FFFSrc"代码页="1252"RowDelimiter="CRLF"IsUnicode="假"FlatFileType="分隔"ColumnNamesInFirstDataRow="true"><列><列姓名=身份证"数据类型="Int32"分隔符=","ColumnType="分隔"/><列名称=值"数据类型="AnsiString"分隔符 =CRLF"输入长度=20"最大宽度="20"长度=20"代码页="1252"ColumnType="分隔"/></列></FlatFileFormat></文件格式><!-- 创建一个使用上面定义的平面文件格式的连接--><连接><平面文件连接名称="FFSrc"文件格式="FFFSrc"FilePath="C:\ssisdata\so\TEST\201306\sample1.txt"延迟验证=真"/></连接><!-- 创建一个包来说明如何在连接管理器上应用表达式--><包><包名称="so_29480267"约束模式=线性"><连接><Connection ConnectionName="tempdb"/><Connection ConnectionName="FFSrc"><表达式><!-- 为 ConnectionString 属性分配一个变量.其语法是 ConnectionManagerName.Property --><Expression PropertyName="FFSrc.ConnectionString">@[User::CurrentFileName]</Expression></表达式></连接></连接><!-- 创建一个指向当前文件的变量--><变量><Variable Name="CurrentFileName" DataType="String">C:\ssisdata\so\TEST\201306\sample1.txt</Variable><Variable Name="Yesterday" DataType="DateTime" EvaluateAsExpression="true">DATEADD("dd", -1, @[System::StartTime])</Variable><Variable Name="YYYYMMDD" DataType="String" EvaluateAsExpression="true">(DT_WSTR, 4)YEAR(@[User::Yesterday])+ RIGHT("0" + (DT_WSTR, 2) MONTH(@[User::Yesterday]), 2)+ RIGHT("0" + (DT_WSTR, 2) DAY(@[User::Yesterday]), 2)</Variable><Variable Name="FileMask" DataType="String" EvaluateAsExpression="true">"FILENAME_" + @[User::YYYYMMDD]+ "_*.csv"</Variable><Variable Name="SourceFolder" DataType="String">C:\ssisdata\so\TEST</Variable><变量名称="RowCountInput" DataType="Int32">0</Variable><Variable Name="TargetTable" DataType="String">[dbo].[so_29480267]</Variable></变量><!-- 添加一个 foreach 文件枚举器.使用上面的 --><任务><ForEachFileLoop名称="FELC 消费文件"文件规范="*.csv"ProcessSubfolders="true"RetrieveFileNameFormat="FullyQualified"文件夹="C:\"约束模式=线性"><!-- 定义表达式以制作输入文件夹和文件掩码由变量值驱动 --><表达式><Expression PropertyName="Directory">@[User::SourceFolder]</Expression><Expression PropertyName="FileSpec">@[User::FileMask]</Expression></表达式><变量映射><!-- 请注意，我们在这里使用了 User.Variable 名称的约定--><变量映射名称="0"变量名="用户.当前文件名"/></VariableMappings><任务><Dataflow Name="DFT 导入文件" DelayValidation="true"><转换><FlatFileSource Name="FFS Sample" ConnectionName="FFSrc"/><RowCount Name="RC Source" VariableName="User.RowCountInput"/></转换></数据流></任务></ForEachFileLoop></任务></包></包></Biml>

I am working on an SSIS package that loads data from a CSV file into a staging table in SQL Server. The CSV file will be in the following format:

FILENAME_YYYYMMDD_HHMMSS.csv

The YYMMDD portion will always correspond to yesterday's date, but the HHMMSS piece will vary depending on exactly when the CSV file was created.

Is there a way to use a script component in SSIS to do the following:

  1. Check to see if there was a file written yesterday (YYYYMMDD = yesterday's date)
  2. If so, store the full filename to a variable

解决方案

I see this as an application of other answers

The first link shows how we'd use an expression to build out a YYYYMMDD string. You need yesterday's date so I'd extend that answer by either applying dateadd("dd", -1, @[System::StartTime) 3 times or I'd add a third Variable, Yesterday and then reference it. In this answer, I will add the Yesterday variable and use it.

The second link covers using a Foreach (file) enumerator. The loop takes as FileSpec filter. We'll use a Variable to hold this value and that Variable will have an expression applied 

"FILENAME_" + @[User::YYYYMMDD]+ "_*.csv"

That's our hard coded file name with the Variable we created in the first step and then an an asterisk to handle the variable time component.

When the foreach loop runs, it's going to populate the fully qualified path name into a variable called CurrentFileName

We use that as an Expression on our Flat File Connection Manager to point it to the correct file prior to import.

It looks like a lot but once you play with it, it shouldn't seem so bad.

Biml

The biml to create this package is nearly identical to the one posted on the second linked post. The place to look changes is the expression for the FileSpec.

How do you use this?

  • Put a file out at C:\ssisdata\so\TEST\201306\sample1.txt (or fix all references to that to be somewhere else)

File should look like

ID,value
1,text here

  • Install BIDS Helper Free add-on to improve your BI life
  • In an IS project, right click on the project and add a new biml file
  • Double click BimlScript.biml and add the contents below replacing everything
  • Save
  • Right click on the BimlScript.biml file and select Generate SSIS package

Profit

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- Create a basic flat file source definition -->
    <FileFormats>
        <FlatFileFormat
            Name="FFFSrc"
            CodePage="1252"
            RowDelimiter="CRLF"
            IsUnicode="false"
            FlatFileType="Delimited"
            ColumnNamesInFirstDataRow="true"
        >
            <Columns>
                <Column
                    Name="ID"
                    DataType="Int32"
                    Delimiter=","
                    ColumnType="Delimited"
                />
                <Column
                    Name="value"
                    DataType="AnsiString"
                    Delimiter="CRLF"
                    InputLength="20"
                    MaximumWidth="20"
                    Length="20"
                    CodePage="1252"
                    ColumnType="Delimited"
                    />
            </Columns>
        </FlatFileFormat>
    </FileFormats>

    <!-- Create a connection that uses the flat file format defined above-->
    <Connections>
        <FlatFileConnection
            Name="FFSrc"
            FileFormat="FFFSrc"
            FilePath="C:\ssisdata\so\TEST\201306\sample1.txt"
            DelayValidation="true"
        />
    </Connections>

    <!-- Create a package to illustrate how to apply an expression on the Connection Manager -->
    <Packages>
        <Package
            Name="so_29480267"
            ConstraintMode="Linear"
        >
            <Connections>
                <Connection ConnectionName="tempdb"/>
                <Connection ConnectionName="FFSrc">
                    <Expressions>
                        <!-- Assign a variable to the ConnectionString property. 
                        The syntax for this is ConnectionManagerName.Property -->
                        <Expression PropertyName="FFSrc.ConnectionString">@[User::CurrentFileName]</Expression>
                    </Expressions>
                </Connection>
            </Connections>

            <!-- Create a single variable that points to the current file -->
            <Variables>
                <Variable Name="CurrentFileName" DataType="String">C:\ssisdata\so\TEST\201306\sample1.txt</Variable>
                <Variable Name="Yesterday" DataType="DateTime" EvaluateAsExpression="true">DATEADD("dd", -1, @[System::StartTime])</Variable>
                <Variable Name="YYYYMMDD" DataType="String" EvaluateAsExpression="true">(DT_WSTR, 4)YEAR(@[User::Yesterday]) 
+ RIGHT("0" + (DT_WSTR, 2) MONTH(@[User::Yesterday]), 2) 
+ RIGHT("0" + (DT_WSTR, 2) DAY(@[User::Yesterday]), 2)</Variable>
                <Variable Name="FileMask" DataType="String" EvaluateAsExpression="true">"FILENAME_" + @[User::YYYYMMDD]+ "_*.csv"</Variable>
                <Variable Name="SourceFolder" DataType="String">C:\ssisdata\so\TEST</Variable>
                <Variable Name="RowCountInput" DataType="Int32">0</Variable>
                <Variable Name="TargetTable" DataType="String">[dbo].[so_29480267]</Variable>
            </Variables>

            <!-- Add a foreach file enumerator. Use the above -->
            <Tasks>

                <ForEachFileLoop
                    Name="FELC Consume files"
                    FileSpecification="*.csv"
                    ProcessSubfolders="true"
                    RetrieveFileNameFormat="FullyQualified"
                    Folder="C:\"
                    ConstraintMode="Linear"
                >
                    <!-- Define the expressions to make the input folder and the file mask 
                    driven by variable values -->
                    <Expressions>
                        <Expression PropertyName="Directory">@[User::SourceFolder]</Expression>
                        <Expression PropertyName="FileSpec">@[User::FileMask]</Expression>
                    </Expressions>
                    <VariableMappings>
                        <!-- Notice that we use the convention of User.Variable name here -->
                        <VariableMapping
                            Name="0"
                            VariableName="User.CurrentFileName"
                        />
                    </VariableMappings>
                    <Tasks>
                        <Dataflow Name="DFT Import file" DelayValidation="true">
                            <Transformations>
                                <FlatFileSource Name="FFS Sample" ConnectionName="FFSrc"/>
                                <RowCount Name="RC Source" VariableName="User.RowCountInput"/>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

这篇关于在 SSIS 中使用 C# 脚本获取时间戳 CSV 的文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆