CSV Files Import Automation

Automatically generate SSIS staging tables, flat file connections, file formats, and import packages for every delimited file in a directory. Use this when you have a folder full of CSV (or tab-delimited) files with consistent header rows and want a one-shot Biml build to land each file into its own staging table.

The snippet is split into two BimlScripts that you run in order:

1. 'Create Staging tables.biml' iterates every file in the configured 'rootFolder', reads the header row to derive column names, and emits an 'ExecuteSQL' task that drops and recreates a 'staging.[<filename>]' table with one 'NVARCHAR(2000)' column per header. All tasks are emitted into a single 'CreateStagingTables' package.
2. 'Create Transformation.biml' emits, for each file, a 'FlatFileConnection', a 'FlatFileFormat' (with column metadata derived from the headers), and a per-file 'Package' containing a 'Dataflow' that reads the file via 'FlatFileSource' and writes it to the matching staging table via 'OleDbDestination'.

Edit the 'rootFolder', file pattern, delimiter, and code page variables at the top of each script to match your source files. Both scripts target a single 'Demo_Connection' OLE DB connection, so point this at your staging database.

The script is designed to run in either BimlStudio or in VSCode with BimlExpress.

<!-- 1.00 Create Staging tables.biml-->
<!--This script can be executed at MIST or at Visual Studio Data Tools-->
<!--This script creates staging tables dynamically-->

<#@import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="Demo_Connection" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Demo;Data Source=."/>
  </Connections>
  <Packages>
    <Package Name="CreateStagingTables" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>
        <#
//var rootFolder = @"C:\Data\Sample2";
//var filesCollection = Directory.GetFiles(rootFolder, "*.csv");
//char splitter = ',';
var rootFolder = @"C:\Data\Sample1";
var filesCollection = Directory.GetFiles(rootFolder, "*.txt");
char splitter = '	';
var createStagingTableScript = String.Empty;
foreach(var fileWithPath in filesCollection)
{
  var reader = new StreamReader(fileWithPath);
  var headers = reader.ReadLine().Split(splitter).ToList();
  createStagingTableScript = string.Format(@"IF OBJECT_ID(N'staging.[{0}]', N'U') IS NOT NULL
                          DROP TABLE staging.[{0}];
                          GO
                          CREATE TABLE [staging].[{0}](COLUMNS)",
                          Path.GetFileNameWithoutExtension(fileWithPath));

  string columns = String.Empty;

  foreach(var header in headers)
  {
    columns+= string.Format("[{0}] NVARCHAR(2000), ", header);
  }
  columns = columns.Substring(0, columns.Length - 2);
  createStagingTableScript = createStagingTableScript.Replace("COLUMNS", columns);

  #>
        <ExecuteSQL Name="Create staging.<#=Path.GetFileNameWithoutExtension(fileWithPath) #>" ConnectionName="Demo_Connection">
          <DirectInput><#=createStagingTableScript#></DirectInput>
        </ExecuteSQL>
        <#} #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

<!-- 2.00 Create Transformation.biml-->
<!--This script can be executed at MIST or at Visual Studio Data Tools-->
<!--This script creates csv import package for each csv file-->
<#@ import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <!--	<Connection Name="Demo_Connection" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Demo;Data Source=."/>
  -->
    <#
     var rootFolder = @"C:\Data\Sample1";
     var fileExtension = "*.txt";
     char csvSplitter = '	';
     var columnDelimeter = "Tab";
     var rowDelimeter = "CRLF"; //CRLF


     // var rootFolder = @"C:\Data\Sample2";
     //var fileExtension = "*.csv";
    //  char csvSplitter = ',';
     // var columnDelimeter = "Comma"; 
     // var rowDelimeter = "CR";

     var headerRowDelimiter = "CRLF";
     var IsUnicode = "false";
     var filePaths = Directory.GetFiles(rootFolder, fileExtension);
     foreach(var filePathWithName in filePaths)
     {		
       #>
    <FlatFileConnection Name="<#=Path.GetFileNameWithoutExtension(filePathWithName) #>_Connection" FileFormat="<#=Path.GetFileNameWithoutExtension(filePathWithName) #>_FileFormat" FilePath="<#=filePathWithName #>"/>
    <#} #>

  </Connections>



  <FileFormats>
    <#
     foreach(var filePathWithName in filePaths)
     {
      var reader = new StreamReader(File.OpenRead(filePathWithName));
      var headers = reader.ReadLine().Split(csvSplitter).ToList();          		
    #>
    <FlatFileFormat Name="<#=Path.GetFileNameWithoutExtension(filePathWithName) #>_FileFormat"  CodePage="1252" IsUnicode="<#= IsUnicode#>" ColumnNamesInFirstDataRow="true" RowDelimiter="<#=rowDelimeter #>" HeaderRowDelimiter="<#=headerRowDelimiter #>">

      <Columns>
        <#
        for(int index=0; index< headers.Count; index++)
        {
        #>
        <Column Name="<#=headers[index] #>" DataType="String" Length="1000" Delimiter="<#= index==headers.Count-1 ?  rowDelimeter : columnDelimeter#>"/>
        <#} #>
      </Columns>
    </FlatFileFormat>
    <#} #>
  </FileFormats>



  <Packages>
    <#
     foreach(var filePathWithName in filePaths)
     {
      var reader = new StreamReader(File.OpenRead(filePathWithName));
      var headers = reader.ReadLine().Split(csvSplitter).ToList();          		
    #>
    <Package Name="<#=String.Format("Import {0} file",  Path.GetFileNameWithoutExtension(filePathWithName)) #>" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>

        <Dataflow Name="Upload_data_to_staging_<#=Path.GetFileNameWithoutExtension(filePathWithName)#>">
          <Transformations>
            <FlatFileSource Name="<#=String.Format("{0}_file_source", Path.GetFileNameWithoutExtension(filePathWithName)) #>" LocaleId="None" ConnectionName="<#=String.Format("{0}_Connection",Path.GetFileNameWithoutExtension(filePathWithName)) #>" />
            <OleDbDestination Name="<#=String.Format("staging_{0}_destination", Path.GetFileNameWithoutExtension(filePathWithName)) #>" LocaleId="None" ConnectionName="Demo_Connection" MaximumInsertCommitSize="2147483647" DefaultCodePage="1252" FastLoadOptions="">
              <InputPath OutputPathName="<#=String.Format("{0}_file_source.Output", Path.GetFileNameWithoutExtension(filePathWithName)) #>" SsisName="OLE DB Destination Input" />
              <ExternalTableOutput Table="staging.[<#=Path.GetFileNameWithoutExtension(filePathWithName)#>]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <#} #>
  </Packages>
</Biml>