Get Filename Of Timestamped Csv Using C# Script In Ssis

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

Solution 1:

I see this as an application of other answers

• YYYYMMDD pattern Leading Zero with Datestamp in SSIS Expression

• Foreach pattern How to import text files with the same name and schema but different directories into database?

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

<Bimlxmlns="http://schemas.varigence.com/biml.xsd"><!-- Create a basic flat file source definition --><FileFormats><FlatFileFormatName="FFFSrc"CodePage="1252"RowDelimiter="CRLF"IsUnicode="false"FlatFileType="Delimited"ColumnNamesInFirstDataRow="true"
        ><Columns><ColumnName="ID"DataType="Int32"Delimiter=","ColumnType="Delimited"
                /><ColumnName="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><FlatFileConnectionName="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><PackageName="so_29480267"ConstraintMode="Linear"
        ><Connections><ConnectionConnectionName="tempdb"/><ConnectionConnectionName="FFSrc"><Expressions><!-- Assign a variable to the ConnectionString property. 
                        The syntax for this is ConnectionManagerName.Property --><ExpressionPropertyName="FFSrc.ConnectionString">@[User::CurrentFileName]</Expression></Expressions></Connection></Connections><!-- Create a single variable that points to the current file --><Variables><VariableName="CurrentFileName"DataType="String">C:\ssisdata\so\TEST\201306\sample1.txt</Variable><VariableName="Yesterday"DataType="DateTime"EvaluateAsExpression="true">DATEADD("dd", -1, @[System::StartTime])</Variable><VariableName="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><VariableName="FileMask"DataType="String"EvaluateAsExpression="true">"FILENAME_" + @[User::YYYYMMDD]+ "_*.csv"</Variable><VariableName="SourceFolder"DataType="String">C:\ssisdata\so\TEST</Variable><VariableName="RowCountInput"DataType="Int32">0</Variable><VariableName="TargetTable"DataType="String">[dbo].[so_29480267]</Variable></Variables><!-- Add a foreach file enumerator. Use the above --><Tasks><ForEachFileLoopName="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><ExpressionPropertyName="Directory">@[User::SourceFolder]</Expression><ExpressionPropertyName="FileSpec">@[User::FileMask]</Expression></Expressions><VariableMappings><!-- Notice that we use the convention of User.Variable name here --><VariableMappingName="0"VariableName="User.CurrentFileName"
                        /></VariableMappings><Tasks><DataflowName="DFT Import file"DelayValidation="true"><Transformations><FlatFileSourceName="FFS Sample"ConnectionName="FFSrc"/><RowCountName="RC Source"VariableName="User.RowCountInput"/></Transformations></Dataflow></Tasks></ForEachFileLoop></Tasks></Package></Packages></Biml>