EzApi - Define flat file columns from connection string

SSIS / EzApi / C#

So what’s new?

Thanks to the contribution via GitHub from Hadi Fadl there’s a new method available for the flat file connection manager that adds the existing columns in the source file. This is very handy because in the past you would have to define each and every column that existed in the flat file.

I’ve updated the nuget to version 0.8.93.

Please give me your feedback of anything you might think should be improved.

How to use

To show this working I got a small CSV from Mockaroo with random data to use as a source. So we start by creating the object and set it’s properties like this and in the end we simply call the method DefineColumnsInCM:


var _flatFileConnOrigin = new EzFlatFileCM(_package)
{
    Name = "CSV Conn Origin",
    ConnectionString = @"C:\Temp\MOCK_DATA.csv",
    ColumnDelimiter = ",",
    ColumnNamesInFirstDataRow = true,
    DelayValidation = true,
    RowDelimiter = "{CR}{LF}",
    HeaderRowDelimiter = "{CR}{LF}"
};
_flatFileConnOrigin.DefineColumnsInCM(FlatFileFormat.Delimited, true);

A full sample with a conditional splitter

So where’s a full example using a conditional splitter to copy only rows with the “Female” value on the “Gender” column to the destination file.


using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.SSIS.EzAPI;
using System;

namespace EzFlatFileTest
{
    class Program
    {
        static void Main(string[] args)
        {
            //Defining the SSIS package 
            var _package = new EzPackage
            {
                Name = "EzFlatFileTest"
            };
            
            //Defining the source file connection manager
            var _flatFileConnOrigin = new EzFlatFileCM(_package)
            {
                Name = "CSV Conn Origin",
                ConnectionString = @"C:\Temp\MOCK_DATA.csv",
                ColumnDelimiter = ",",
                ColumnNamesInFirstDataRow = true,
                DelayValidation = true,
                RowDelimiter = "{CR}{LF}",
                HeaderRowDelimiter = "{CR}{LF}"
            };
            _flatFileConnOrigin.DefineColumnsInCM(FlatFileFormat.Delimited, true);

            //Defining the destination file connection manager
            var _flatFileConnDest = new EzFlatFileCM(_package)
            {
                Name = "CSV Conn Dest",
                ConnectionString = @"C:\Temp\MOCK_DATA_DEST.csv",
                ColumnNamesInFirstDataRow = true,
                ColumnDelimiter = ",",
                DelayValidation = true,
                RowDelimiter = "{CR}{LF}",
                HeaderRowDelimiter = "{CR}{LF}"
            };
            _flatFileConnDest.DefineColumnsInCM(FlatFileFormat.Delimited, true);
            
            //Adding a dataflow to the package
            var _dataFlow = new EzDataFlow(_package)
            {
                Name = "MyDataFlow"
            };

            //Adding flat file source to the dataflow
            var _flatFileOrigin = new EzFlatFileSource(_dataFlow)
            {
                Name = "Origin CSV",
                Connection = _flatFileConnOrigin
            };

            //Adding flat file destination to the dataflow
            var _flatFileDest = new EzFlatFileDestination(_dataFlow)
            {
                Name = "Dest CSV",
                Connection = _flatFileConnDest,
                Overwrite = true,
            };

            //Adding a conditional split between the flat file origin and destination
            var _conditionalSplit = new EzConditionalSplit(_dataFlow)
            {
                Name = "Split by Gender"
            };
            _conditionalSplit.AttachTo(_flatFileOrigin);
            _conditionalSplit.LinkAllInputsToOutputs();
            
            //Redirect rows with Gender equal to Female to pipeline "Case 1"
            _conditionalSplit.Condition["Case 1"] = "gender == \"Female\"";

            //Attach destination file to pipeline "Case 1"
            _flatFileDest.AttachTo(_conditionalSplit, 2, 0);
            _flatFileDest.DefineColumnsInCM();

            //Save package to disk
            _package.SaveToFile(_package.Name + ".dtsx");
        }
    }
}

Now we can run it using:


DTExec.exe /F EzFlatFileTest.dtsx

Have fun automating your packages!

 Share!