• Home
        • Database Development

          Database development is designing, creating a database or data model, and analyzing requirements and their intents as raw data.

          Learn More
        • Architecture & Design

          Software architecture refers to the fundamental structures of a software system and the discipline of creating such structures and systems.

          Learn More
        • Programming

          Computer programming is the process of performing a particular computation or more generally, accomplishing a specific result.

          Learn More
        • Cloud Computing

          Cloud computing is the on-demand availability of computer system resources, especially data storage and computing power.

          Learn More
        • ETL Development

          ETL provides the foundation for data analytics and machine learning workstreams. Through a series of business rules, ETL cleanses and organizes data.

          Learn More
        • Data Visualization & Reports

          Data and information visualization is an interdisciplinary field that deals with the graphic representation of data and information.

          Learn More
  • Blog
  • Contact

Programmatically Import Flat File Data Source into SQL Server 2008 Using SSIS’s Script Task Tool

This article demonstrates creating a SQL Server Integration Services package that imports a text file into SQL Server database table using a Script Task component.

We begin by launching Microsoft Visual Studio and creating a new Integration Services Project which is located under Business Intelligence Projects category.
After you have assigned a project name, proceed to click and drag the Script Task in the Control Flow pane of the package’s toolbox.
Right-click the script task and click on “Edit”
Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.
In Project Explorer, ensure the following references are added:
Back to the code window, ensure that the following namespaces are declared:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
After the above declarations, proceed to create a new application instance:
Application selectSIFISO_app = new Application();
Create package:
Package sS_pkg = new Package();
Assign relevant package name and description:
sS_pkg.Name = “Load Flat File Source into OLE DB Destination Using C#”;
sS_pkg.Description = “Programmatically create an SSIS 2008 package that loads a Flat File Source into OLE DB Destination Using Script Task’s C# language”;

Insert the Data Flow Task with an appropriate name and some buffer space for processing of file (the last part is optional – you can also use default buffer allocation):
TaskHost taskHost = sS_pkg.Executables[0] as TaskHost;
MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
taskHost.Name = “Dynamic Data Flow Task”;
taskHost.Properties[“DefaultBufferMaxRows”].SetValue(taskHost, “1000000”);

Insert the Flat File connection:
ConnectionManager connectionManagerFlatFile = sS_pkg.Connections.Add(“FLATFILE”);
You can change this path depending on where you have stored the flat file (ensure you download the attached file, see “Requirements” section above):
connectionManagerFlatFile.ConnectionString = @”C:\Temp\flat_src.txt”;
Assign a name to the flat file connection:
connectionManagerFlatFile.Name = “TXT_FlatFile”;
Indicate that the flat file is delimited:
connectionManagerFlatFile.Properties[“Format”].SetValue(connectionManagerFlatFile, “Delimited”);
Indicate whether the source file has column headings or not – in this case, our sample data has column headings, hence – true:
connectionManagerFlatFile.Properties[“ColumnNamesInFirstDataRow”].SetValue(connectionManagerFlatFile, Convert.ToBoolean(true));
Get a native Flat File connection:
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
Declare the local string variable that will be used as part of reading the text file:
string line;
Determine the number of columns by reading the sample Flat File – line by line:

using (StreamReader file = new StreamReader(@"C:\Temp\flat_src.txt"))
		  while ((line = file.ReadLine()) != null)
			  char[] delimiters = new char[] { '|' };
			  string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
  for (int i = 0; i < parts.Length; i++)
	   RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol
	   = connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
	   sS_AssignColumnProperties(flatFileCol, parts[i], "|");
			  //Exit file after reading the first line
	catch (Exception ex)
		   throw ex;

Edit the last Flat File column delimiter into NewLine instead of a Comma:
connectionFlatFile.Columns[connectionFlatFile.Columns.Count – 1].ColumnDelimiter = Environment.NewLine;
Insert Flat File source component:
IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
componentSource.Name = “FlatFileSource”;
componentSource.ComponentClassID = “DTSAdapter.FlatFileSource”;

Insert source design-time instance and initialize component:
CManagedComponentWrapper instanceSource = componentSource.Instantiate();

Set source connection:
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFlatFile);

Reinitialize Flat File source metadata:

Insert the SQL Server 2008 OLE-DB connection:
ConnectionManager connectionManagerOleDb = sS_pkg.Connections.Add(“OLEDB”);
connectionManagerOleDb.ConnectionString = string.Format(“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “localhost”, “AdventureWorks”);
connectionManagerOleDb.Name = “OLEDB”;
connectionManagerOleDb.Description = “OLEDB Connection”;

Insert OLE-DB destination:
IDTSComponentMetaData100 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
componentDestination.Name = “OLEDBDestination”;
componentDestination.Description = “OLEDB Destination for the Flat File data load”;
componentDestination.ComponentClassID = “DTSAdapter.OLEDBDestination”;

Insert destination design-time instance and initialize component:
CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();

Set destination connection:
componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
componentDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerOleDb);

Indicates the name of the database object used to open a rowset:
instanceDestination.SetComponentProperty(“OpenRowset”, “[dbo].[sS_flatfileLoad]”);
Specifies the mode used to open the database:
instanceDestination.SetComponentProperty(“AccessMode”, 3);
Specifies options to be used with the fast load. Applies only if fast load is turned on:
instanceDestination.SetComponentProperty(“FastLoadOptions”, “TABLOCK,CHECK_CONSTRAINTS”);
Indicates whether the values supplied for identity columns will be copied to the destination or not
In this case, we have set this property to false:
instanceDestination.SetComponentProperty(“FastLoadKeepIdentity”, false);
Indicates whether the columns containing null will have null inserted in the destination or not
In this case, we have opted not to insert nulls:
instanceDestination.SetComponentProperty(“FastLoadKeepNulls”, false);
Specifies the column code page to use when code page information is unavailable from the data source
In this case, we used the default – 1252:
instanceDestination.SetComponentProperty(“DefaultCodePage”, 1252);
Specifies when commits are issued during data insertion
In this case, we have opted for the default size which is set to 2147483647:
instanceDestination.SetComponentProperty(“FastLoadMaxInsertCommitSize”, 2147483647);
Indicates the number of seconds before a command times out
In this case, we have opted for the default value of 0 which indicates an infinite time-out:
instanceDestination.SetComponentProperty(“CommandTimeout”, 0);
Indicates the usage of DefaultCodePage property value when describing the character data
In this case, we have opted for the default value of false:
instanceDestination.SetComponentProperty(“AlwaysUseDefaultCodePage”, false);
Connect the Flat File source to the OLE DB Destination component:

Get input and virtual input for the destination to select and map columns:
IDTSInput100 destinationInput = componentDestination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInput = destinationInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns = destinationVirtualInput.VirtualInputColumnCollection;

Reinitialize the metadata, generating external columns from flat file columns:

Select and map destination columns:
foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumns)
// Select column, and retain new input column
IDTSInputColumn100 inputColumn = instanceDestination.SetUsageType(destinationInput.ID,destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
// Find external column by name
IDTSExternalMetadataColumn100 externalColumn = destinationInput.ExternalMetadataColumnCollection[inputColumn.Name];
// Map input column to external column
instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID);

Execute the package or disable the below code if you intend to run the package later:
Finally, save the package – in this case, we have opted to save the package into the file system:
selectSIFISO_app.SaveToXml(@”E:\newArticle.dtsx”, sS_pkg, null);
Dts.TaskResult = (int)ScriptResults.Success;

In addition to the above code, you will notice that some part of the code references the below function. This function is used to assign DTS column properties:
private static void sS_AssignColumnProperties(RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol, string getColName, string getDelim)

Assign delimiter:
flatFileCol.ColumnType = “Delimited”;
flatFileCol.ColumnDelimiter = getDelim;

Indicate column data type – in this case, all the source columns will be set to String Data Type:
flatFileCol.DataType = RuntimeWrapper.DataType.DT_STR;
Indicate column width – in this case, the width of all source columns will be set to a length of 100:
flatFileCol.ColumnWidth = 100;
Assign column name:
RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;
columnName.Name = getColName.ToString();

It’s that simple!
You can now execute your script task and the package will be created in the location you specified.
For the complete C# solution to this article, see ” Requirements ” section for a file name called “selectSIFISO_Flat_File_Source_into_OLE_DB_Destination_Using_CSharp.txt”.



October 14, 2022
Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

Meet Our Experts

We are proud to have a team of experts who are passionate about delivering the best possible solutions to you. Our team members are highly skilled and experienced in a diverse range of IT technologies, and are committed to staying up-to-date with the latest industry trends and best practices to deliver you the best results.


Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join Our Newsletter

Subscribe to get our latest and best thinking on the most definitive workforce topics affecting HR leaders and organizations today.