Programmatically add or modify a FlatFile-Source component


Intro

 

In the first article of this series I painted the high level picture and provided an overview of the package generation framework. In this and the following articles I want to show how to programmatically generate or modify specific SSIS components.

FlatFile Source Component

The first step is to find the connection manager component to modify or to add a new connection manager to the package.

All package connection managers can be found in the Connections collection of the Microsoft.SqlServer.Dts.Runtime.Package class.

So the first thing to do is to find the Connection Manager you want to edit.

foreach (ConnectionManager connectionManager in _Package.Connections)

            {

                if (connectionManager.Name.Equals("SourceFlatFileCM"))

                {
                   

In the case that you want to add a Connection Manager programmatically versus editing an existing one you can use the following code.

ConnectionManager conManager = _Package.Connections.Add("FLATFILE");
IDTSConnectionManagerFlatFile100 connectionFlatFile = (IDTSConnectionManagerFlatFile100)conManager.InnerObject;

A list with all the viable parameters for the Add procedure can be found on this page: http://msdn.microsoft.com/en-us/library/ms136093.aspx

Setting the necessary configurations on the Connection Manager is the next step. There are some generally valid properties and a lot of Connection Manager specific properties. The general properties are members of Connection Manger class whilst the specific properties can be set by acquiring an actual instance of the specific Connection Manager object (oleDbConnectionManager in the above example).

In my simple example I set some hardcoded properties just do demonstrate the use of the Connection Manager.

connectionManager.Name = "SourceFlatFileCM";

connectionManager.ConnectionString = "C:\Data\TestData.CSV";

 

             IDTSConnectionManagerFlatFile100 connectionFlatFile

         = (IDTSConnectionManagerFlatFile100)connectionManager.InnerObject;

             connectionFlatFile.RowDelimiter = "\n";

       connectionFlatFile.Format = "Delimited";

connectionFlatFile.ColumnNamesInFirstDataRow = true;

       connectionFlatFile.LocaleID = 1033;

       connectionFlatFile.CodePage = 1252;

       connectionFlatFile.RowDelimiter = "\n";

The next step is just needed if you are modifying an existing package and you want set up the column definition from scratch.

// remove existing columns

      foreach (IDTSConnectionManagerFlatFileColumn100 column in connectionFlatFile.Columns)

{

connectionFlatFile.Columns.Remove(column);

}

The final step is to add the column definitions to the Connection Managers Columns collection.

The demo code shows how to add a FlatFileColumn to the collection.

IDTSConnectionManagerFlatFileColumn100 flatFileColumn

= (IDTSConnectionManagerFlatFileColumn100)connectionFlatFile.Columns.Add();

flatFileColumn.ColumnType = "Delimited";

flatFileColumn.ColumnWidth = 0;

flatFileColumn.MaximumWidth = 100;

flatFileColumn.DataPrecision = 0;

flatFileColumn.DataScale = 0;

flatFileColumn.TextQualified = false;

IDTSName100 columnName = (IDTSName100) flatFileColumn;
columnName.Name =
"CustomerName";

flatFileColumn.ColumnDelimiter = "\n"; // for the last column in the row

In a real world example the above step will be done in a loop to add all the desired columns. An important point is to remember to set the ColumnDelimiter property of the last column of the row to the row delimiter.


Comments (1)
  1. Anonymous says:

    British reporters are known for doing almost anything to get a Mulberry Bags. But reports that a newspaper secretly listened to telephone messages of murdered schoolgirls and other private citizens have produced Mulberry Handbags and anger.

    On Friday, British police arrested Andy Coulson, former editor of Mulberry Bag Britain's best-selling newspaper, News of Mulberry Outlet the World. The investigation led him to Mulberry UK Sale resign in January as communications director to Prime Minister David Cameron.

    The arrest came in a widening investigation of Mulberry UK telephone hacking. Other accusations include paying police for mulberry shoulder bags information on stories. The Reuters news agency reported that Mr. Coulson was released on Mulberry Bag UK until a date in October.

    Prime Minister Cameron promised Men's Mulberry Bags Friday that a judge will lead a full public inquiry into Women's Mulberry Bags the case after police complete their investigation.

    DAVID CAMERON: "Murder victims, terrorist victims, families who have lost loved ones, sometimes defending our country, that these people could have had their phones hacked into, in order to generate stories for Mulberry Bags  Mulberry Handbags  Mulberry Bag   Mulberry Outlet  Mulberry UK  mulberry bayswater bag  Mulberry Alexa Bag, is simply disgusting."

Comments are closed.

Skip to main content