Note – This is going over what it takes to create a simple data flow task in SSIS using Biml. If you require Biml setup and more details about what Biml is, please go to the Biml websites for more information. You can find helpful ones here and here.

In this example, we’ll be exporting customers from a database into two files, based on gender. For an extra conditional split, we’re going to say if a customer’s first name is Nettie, Daven, or Luce, it’s an error.

Connections

We’ll need to create 4 connections

1 – OleDbConnection to the database
2 – FlatFileConnection for Female customers
3 – FlatFileConnection for Male customers
4 – FlatFileConnection for Errors

For each flat file connection, we’re going to need to create a FlatFileFormat which determines the columns in the files. You can see these formats created in the code above.

Notes:

The CreateInProject attribute for each connection tells the compiler to create the connection within the project or each package that it is used
Since the Female and Male files have the same format, we only need one FlatFileFormat

Packages

Now with our connections created, we can go ahead and create the package with our data flow task:

Notes:

Notice how <FlatFileDestination ConnectionName> attribute is using connection name “ErrorFlatFileConnection” created in the previous section
<FlatFileDestination>.<InputPath OutputPathName> attribute is using an OutputPathName that is created from ConditionalSplit.OutputPaths element
If <Columns> are not specified for <FlatFileDestination>s, the compiler will implicitly map what it can where column names exactly match