SSIS–DFT does not loads last row from flat file

Yesterday, I had an interesting issue.  It has been reported me that an ETL always misses the last row from the flat file.  No matter what is the row is it happens for every file that the last row is missing.

It is wired issue, I started check the ETL and created a new ETL with DFT pointing the same share file.  I could able to get all the rows.  It was happening only in that particular ETL.

I copied the DFT from the error ETL to my new ETL.  The problem disappeared.  I was wondering about the magic happening here.  The DFT is same, data file is same but the last row is getting missed.

I started comparing the ETL config and connections.  oh…at last I could able to figure out the issue.  It was due the Text Qualifier setting which update on the Flat File Connection component.  Make sure it set to None, if you don’t have any special handling.

Related MSDN discussion

SSIS Programming reference

Generating SSIS using meta data is a powerful technique when you want to move large number of table from source to target.  In this post I will highlight the programming references to create the SSIS dynamically.

• Microsoft.SqlServer.Dts.Runtime.Package is the highlevel object which hold all the objects of the package.  You can create New Package object to start building you object mode.
• Connections collection holds connections. 
  Example: PackageObj.Connections.Add("OLEDB");
  Refer MSDN for list of connection types
• You can change the custom property of the connection by accessing the Property collection of the connection
  Example: ConnectionObj.Properties["Format"].SetValue(csvFile, "Delimited");
• Executable: By accessing the Executable collection you can add DFT, SQL Task or any other task to Package.
  Example: Package.Executables.Add(“STOCK:SEQUENCE”);
  For list of moniker, refer the SQLIS blog
• When you are dealing with Data Flow Task (DFT), it is complex due to the fact DFT are COM and we have a CManagedComponentWrapper to access them.
• The hierarchy of object structure is
• Executable
• TaskHost
• MainPipe
• IDTSComponentMetaData100

Package p = new Package();
Executable e = p.Executables.Add("DTS.Pipeline.1");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

• In DFT, you can add pipeline items by using ComponentMetaDataCollection of MainPipe object

IDTSComponentMetaData100 pipeLineItem = MainPipeObj.ComponentMetaDataCollection.New();
pipeLineItem.ComponentClassID = "DTSAdapter.OleDbSource.2";

• When you are using the Source object in PipeLine, make sure it get initiated using IDTSComponentMetaData100.Instantiate & CManagedComponentWrapper.ProvideComponentProperties methods.  These methods are explicit interface implementation so make sure necessary type cast done when you call the methods.

CManagedComponentWrapper InstanceSource = IDTSComponentMetaData100Obj.Instantiate();
InstanceSource.ProvideComponentProperties();

• For Source, Destination tasks make sure the connection are associated.

public static void SetConnection(
IDTSComponentMetaData100 obj, ConnectionManager con)
{
obj.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(con);
obj.RuntimeConnectionCollection[0].ConnectionManagerID = con.ID;
}

• After the connection is set, it needs to be refreshed to get the meta data

CManagedComponentWrapperObj.AcquireConnections(null);
CManagedComponentWrapperObj.ReinitializeMetaData();
CManagedComponentWrapperObj.ReleaseConnections();

• You can attach multiple item to pipeline. 
• To link the pipeline objects, you can use Attach Path method

IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0], target.InputCollection[0]);

• To map the columns between pipeline items, you need to use the column collections

public void MapColumns(
IDTSComponentMetaData100 DestinationTask
, CManagedComponentWrapper InstanceDestination
, DTSUsageType dtsUsageType)
{
IDTSInput100 input = DestinationTask.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSInputColumn100 vCol = null;

if (dtsUsageType == DTSUsageType.UT_READONLY)
{
foreach (IDTSVirtualInputColumn100 vColumn in  vInput.VirtualInputColumnCollection)
{
  InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
}

foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[col.Name];
InstanceDestination.MapInputColumn(input.ID, col.ID, exCol.ID);
}
}
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[vColumn.Name];
InstanceDestination.MapInputColumn(input.ID, vCol.ID, exCol.ID);
                    }
                }
        }

• For connecting executable in Control Tasks, you can use PrecedenceConstraint class to associate the Task for sequential execution.

PrecedenceConstraint prePC = DFT.ParentContainer.PrecedenceConstraints.Add(preExec, postExec);