Abstract
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.
Requirements
- Microsoft Visual Studio 2008
- SQL Server 2005 (or later editions)
- create_table_script.txt
- flat_src.txt
- selectSIFISO_Flat_File_Source_into_OLE_DB_Destination_Using_CSharp.txt
Article
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:
Microsoft.SqlServer.Dts.Design;
Microsoft.SqlServer.DTSPipelineWrap;
Microsoft.SQLServer.DTSRuntimeWrap;
Microsoft.SqlServer.ManagedDTS;
Microsoft.SqlServer.ScriptTask;
System;
System.AddIn;
System.Data;
System.Windows.Forms;
System.Xml;
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):
sS_pkg.Executables.Add(“STOCK:PipelineTask”);
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"))
{
try
{
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
break;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
file.Close();
}
}
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();
instanceSource.ProvideComponentProperties();
Set source connection:
componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFlatFile);
Reinitialize Flat File source metadata:
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();
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();
instanceDestination.ProvideComponentProperties();
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:
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(componentSource.OutputCollection[0]
,componentDestination.InputCollection[0]);
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:
instanceDestination.AcquireConnections(null);
instanceDestination.ReinitializeMetaData();
instanceDestination.ReleaseConnections();
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:
sS_pkg.Execute();
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();
}
Conclusion
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”.
Thanks.
Sifiso.
I have followed your instructions, and have been unable to successfully run the task. I have made the following changes:
c:\temp\
changed to:
C:\Data\Download\DotNetCode\c#stuff\ImportFlatFileDataSourceIntoSqlServer\
connectionManagerOleDb.ConnectionString = string.Format(“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “SIFISOWIN7-PC”, “AdventureWorks”);
changed to:
connectionManagerOleDb.ConnectionString = string.Format(“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “MFULF4\\DSIS”, “TEMP_OUTPUT”);
The error message is:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Runtime.InteropServices.COMException (0xC02020E8): Exception from HRESULT: 0xC02020E8
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at ST_94a9f0913c0a487aa85d3139c2ca8c59.csproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
I would appreciate any assistance you can provide, as I am learning on the fly.
Thanks.
Everett Music
I will have a look and advise shortly
Hi There,
So the error you getting is about failure to re-initialise the metadata but unfortunately the error message doesn’t indicate whether was it failure to re-initialise source or destination metadata.
In order to determine whether it’s failing on source or destination, can rather insert breakpoints inside the code and run script task in debug mode.
Can you also ensure that you specified a correct output table in the line:
instanceDestination.SetComponentProperty(“OpenRowset”, “[dbo].[sS_flatfileLoad]”);
Also, on your output table, please ensure that the column names are similar to that of source file. This is because the mappings are done by column name.
Let me know how it goes.
Cheers.
Sifiso
Hi,
This code works fine for perfect input source.Meaning if i have 10 rows.
First two rows with 4 column
3rd and 4th rows with 2 column and
last 6 rows with 4 column.
Then the Destination table has only First two rows. Remaining rows are getting ignored even it is ignoring last 6 rows with 4 column also. Any idea to solve this issue. pls advice.
Thanks in Advance
Akshaya
Hi Akshaya,
I appreciate the feedback.
Just recreated the error you have been getting.
Refer to the below code, I try to determine the number of columns to insert into flat file connection by getting a count of pipe delimited columns/cells on the first row. The assumption is that if I get a total of 6 cells, then the rest of the rows should contain 6 cells regardless whether there is content or not. For example the assumption is as such:
|heading 1|heading 2|heading 3|heading 4|heading 5|heading 6
|a||c|d|e|f
|a|b||d||f
Take a look at the example I have provided above, letter b is missing from the record below headings but the count of expected cells/columns still adds up to 6.
So I suppose my advise would be, where you don’t have entries for respective columns you should have nulls or leave empty but the column delimiters must be distributed evenly. If the file you are sourcing was outputted by some other system (i.e. SAP etc.) they normally insert null entries where there is no data but columns are distributed evenly.
I hope this further explains the purpose of this post/approach.
BUT, if you have a scenario where you getting data with incompatible number of columns, then either you put some check to have that rectified before running this script. Another way would to loop through individual row, create an output table based on number of columns sourced. Meaning in your example of 10 rows, you will have 10 different tables. Or you can have two output table but prior to looping each row, you will have to check the count of columns and compare to previous row such that when you get to the rows with 2 columns you output to another table – or you programmatically insert a column with null to fill the missing 2 columns.
So, yes, there are definitely ways to deal with the issue you raised but it can be expensive and time-consuming.
You know what, come to think of it, I will create another post that will address inserting null entries where the source data columns are varying.
watch this space. thanks again for your feedback and apologies for a mouthful response.
using (StreamReader file = new StreamReader(@”C:\Temp\flat_src.txt”))
{
try
{
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 break; } } catch (Exception ex) { throw ex; } finally { file.Close(); } }
Hey there,
I have successfully used your code. Works fine for me…thanks a lot.
Can you please suggest some approach for using Unpivot data transformation programmatically. Thanks again….have a nice one…
Hi,
The code you have posted is nice one. I have used your code works fine for me. But i have the flat file column names and data with double quotes. Flat file likes this below,
“ID”,”FirstName”,”LastName”
“1”,”Name1″,”Name2″
I would appreciate any assistance you can provide, thanks in advance.
Thanks,
Ravi
Hi Ravi,
I will get back to you shortly.
Regards,
Sifiso
hi Ravi,
replace the stream reader script with the one below. I have replaced the pipe delimiter with comma and did a substring. I have test ran it and it runs fine. it’s just that the row data still have double quotes – that’s because I break after reading 1 line of record. You might have to insert a data conversion/derived component to remove the quotes.
using (StreamReader file = new StreamReader(@”C:\Temp\flat_src.txt”))
{
try
{
while ((line = file.ReadLine()) != null)
{
char[] delimiters = new char[] { ‘,’ };
string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
string frmt=””;
for (int i = 0; i < parts.Length; i++) { RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol = connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100; frmt = parts[i].Substring(1, (parts[i].Length - 2)); sS_AssignColumnProperties(flatFileCol, frmt, ","); } //Exit file after reading the first line break; } } catch (Exception ex) { throw ex; } finally { file.Close(); } }
Hi,
Thanks for your help. Working fine now :).
Thanks,
Ravi
Hi,
I have one more clarification. In my flat file, i have some columns filled with blank values. Here is a sample of it:
“column1″,”column2″,”column3″,”column4”
“1”,”John”,”10″,”09/01/2012 12:00:00 AM”
“2”,”Mark”,””,”09/01/2012 12:00:00 AM”
“3”,”Steve”,””,””
In the above sample, consider the third row containing data (“3″,”Steve”,””,””). In this data, column3 and column4 are empty and in database table, the corresponding columns are of data type numeric and date/time respectively. These columns are declared as nullable as well. But while inserting this data using the above mentioned code, i am getting an error similar to one mentioned below:
Data conversion failed. The data conversion for column “Column3” returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
Can you tell me how to convert string.empty from the flat file as NULL while inserting into database?
Thanks in advance for your help!
Regards,
Ravi
Hi,
Why don’t you stage the data first by importing all data into a table with varchar data types columns?
You can then transform/convert it using T-SQL.
I just think that might be easier, what do you think?
Regards,
Sifiso
Great Article. Thanks a lot for posting this…
pleasure
Hi All,
Is there any solution by suing Excel files.
Thanks
vasu
Hi Sifiso
Great article ! Many thanks for posting.
I’m trying to design the package so it contains a “create table” task first, then a dataflow, fully depending on the flat file contents/layout.
It find your code help me a lot, but seems to me it actually expects the target table to exist already, which is not the case for me.
It fails at this point:
instanceDestination.AcquireConnections(null);
… because of course the table specified as destination in the line
instanceDestination.SetComponentProperty(“OpenRowset”, “[dbo].[sS_flatfileLoad]”);
… does not exist in my case.
Is there a way to, using the same column info derived from the file file, explicitly set the metadata instead of actualling connecting there, and calling ReinitializeMetaData()?
TargetComponentInstance.ReinitializeMetaData()
Thanks again!
Mostard.
Hi Mostard,
Thanks for feedback.
There is definately a way. Let me have a look and I will get back to you shortly.
Thanks,
Sifiso
hey i want a ssis to create package that can create a table and insert all the data from flat file
with delimeters as [tab]
can you please tell me the code .
hey i want a ssis to create package that can create a table and insert all the data from flat file
with delimeters as [tab]
can you please tell me the code .
c# code
that can help .
hey, thanks for feedback. I will get back to you shortly.
hi, did you come right with this?
Do you happen to have anything like this for 2012?
not really, but I can create one for you. What deployment method are you using in 2012? Package or Project?
Hi, I am trying to run this code (below) and I get error on the destination component, at _destinationInstance.ReinitializeMetaData();
and I have found that this is when I have this line: _destinationInstance.SetComponentProperty(“AccessMode”, “0”);
I get an hresult exception.
I have tried all other access modes and nothing works. If I get rid of the accessMode line, the package runs with no errors but nothing is pushed in to the SQL Table.
Any ideas?
Thanks in advance.
using System;
using System.Linq;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Collections.Generic;
using System.Data.Common;
using System.Reflection;
using Microsoft.VisualBasic.FileIO;
using Microsoft.SqlServer.Dts;
namespace ssis
{
public class FlatFileColumnReader
{
public List Columns(string path, char delimiter, FieldType ft)
{
var tfp = new TextFieldParser(path)
{
TextFieldType = ft
};
tfp.Delimiters = new string[] {delimiter.ToString()};
return tfp.ReadFields().ToList();
}
}
public class ssis_run
{
public static Microsoft.SqlServer.Dts.Runtime.Package Generate(Microsoft.SqlServer.Dts.Runtime.Application app)
{
Microsoft.SqlServer.Dts.Runtime.Package _package;
Executable _dataFlowTask;
IDTSComponentMetaData100 _dataSource;
IDTSComponentMetaData100 _dataDest;
CManagedComponentWrapper _sourceInstance;
CManagedComponentWrapper _destinationInstance;
ConnectionManager _conMgrSource;
ConnectionManager _conMgrDest;
_package = new Microsoft.SqlServer.Dts.Runtime.Package();
_package.DelayValidation = true;
_dataFlowTask = _package.Executables.Add(“STOCK:PipelineTask”);
MainPipe pipe = (MainPipe)((Microsoft.SqlServer.Dts.Runtime.TaskHost)_dataFlowTask).InnerObject;
//pipe.ComponentMetaDataCollection.RemoveAll();
/// Create connections
_conMgrSource = _package.Connections.Add(“FLATFILE”);
_conMgrSource.Properties[“Format”].SetValue(_conMgrSource, “Delimited”);
_conMgrSource.Properties[“Name”].SetValue(_conMgrSource, “Flat File Connection”);
_conMgrSource.Properties[“ConnectionString”].SetValue(_conMgrSource, @”G:\DATA\VIS\Rowing\World Racing Performance Prediction Database_01_JUNE_2012 – Copy.csv”);
_conMgrSource.Properties[“ColumnNamesInFirstDataRow”].SetValue(_conMgrSource, true);
_conMgrSource.Properties[“HeaderRowDelimiter”].SetValue(_conMgrSource, “{CR}{LF}”);
//_conMgrSource.Properties[“HeaderRowDelimiter”].SetValue(_conMgrSource, “\r\n”);
_conMgrSource.Properties[“RowDelimiter”].SetValue( _conMgrSource, “{CR}{LF}” );
_conMgrSource.Properties[“TextQualifier”].SetValue(_conMgrSource, “\””);
_conMgrSource.Properties[“DataRowsToSkip”].SetValue(_conMgrSource, 0);
_conMgrDest = _package.Connections.Add(“OLEDB”);
_conMgrDest.ConnectionString = @”Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=RowingRaces;Data Source=localhost”;
//_conMgrDest.ConnectionString = @”Data Source=localhost;Initial Catalog=RowingRaces;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;”;
_conMgrDest.Name = “OLE DB Connection”;
_conMgrDest.Description = “OLE DB Connection”;
_conMgrDest.Properties[“RetainSameConnection”].SetValue(_conMgrDest, true);
/// Create the columns in the flat file connection
var flatFileConnection = _conMgrSource.InnerObject as IDTSConnectionManagerFlatFile100;
var fileColumns = new FlatFileColumnReader().Columns( @”G:\DATA\VIS\Rowing\World Racing Performance Prediction Database_01_JUNE_2012.csv”, ‘,’, FieldType.Delimited);
for (int i = 0; i < fileColumns.Count; i++)
{
var column = flatFileConnection.Columns.Add();
//column.ColumnDelimiter = (i == fileColumns.Count – 1) ? "\r\n" : ",";
column.ColumnDelimiter = (i == fileColumns.Count – 1) ? "{CR}{LF}" : ",";
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = DataType.DT_TEXT;
column.DataPrecision = 0;
column.DataScale = 0;
( (IDTSName100)column ).Name = fileColumns[i];
}
/// Create Data Flow Components
_dataSource = pipe.ComponentMetaDataCollection.New();
_dataSource.Name = "Flat File Source";
_dataSource.ComponentClassID = app.PipelineComponentInfos["Flat File Source"].CreationName;
_dataSource.ValidateExternalMetadata = false;
_sourceInstance = _dataSource.Instantiate();
_sourceInstance.ProvideComponentProperties();
_dataSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_conMgrSource);
_dataSource.RuntimeConnectionCollection[0].ConnectionManagerID = _conMgrSource.ID;
_sourceInstance.AcquireConnections(null);
_sourceInstance.ReinitializeMetaData();
_sourceInstance.ReleaseConnections();
_dataDest = pipe.ComponentMetaDataCollection.New();
//_dataDest.Name = "Sql Server Destination";
//_dataDest.ComponentClassID = app.PipelineComponentInfos["SQL Server Destination"].CreationName;
_dataDest.Name = "OLE DB Destination";
_dataDest.ComponentClassID = app.PipelineComponentInfos["OLE DB Destination"].CreationName;
_dataDest.ValidateExternalMetadata = true;
_destinationInstance = _dataDest.Instantiate();
_destinationInstance.ProvideComponentProperties();
//_destinationInstance.SetComponentProperty("BulkInsertTableName", "[races]");
_dataDest.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_conMgrDest);
_dataDest.RuntimeConnectionCollection[0].ConnectionManagerID = _conMgrDest.ID;
_destinationInstance.SetComponentProperty("OpenRowset", "[dbo].[races]");
_destinationInstance.SetComponentProperty("AccessMode", "0");
_destinationInstance.AcquireConnections(null);
_destinationInstance.ReinitializeMetaData();
_destinationInstance.ReleaseConnections();
var path = pipe.PathCollection.New();
path.AttachPathAndPropagateNotifications(_dataSource.OutputCollection[0], _dataDest.InputCollection[0]);
//bo
IDTSInput100 destInput = _dataDest.InputCollection[0];
IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;
IDTSOutputColumnCollection100 sourceColumns = _dataSource.OutputCollection[0].OutputColumnCollection;
foreach (IDTSOutputColumn100 outputCol in sourceColumns)
{
IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];
if (extCol != null)
{
destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);
IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);
if (inputCol != null)
{
_destinationInstance.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
}
}
}
//bo
//bo commented out
//var virtualInput = _dataDest.InputCollection[0].GetVirtualInput();
//foreach (IDTSVirtualInputColumn100 column in virtualInput.VirtualInputColumnCollection)
//{
// _destinationInstance.SetUsageType(_dataDest.InputCollection[0].ID, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);
//}
///// MapColumns();
//foreach (IDTSInputColumn100 inputColumn in _dataDest.InputCollection[0].InputColumnCollection)
//{
// var outputColumn = _dataDest.InputCollection[0].ExternalMetadataColumnCollection[inputColumn.Name];
// outputColumn.Name = inputColumn.Name;
// _destinationInstance.MapInputColumn(_dataDest.InputCollection[0].ID, inputColumn.ID, outputColumn.ID);
//}
//bo commented out
_package.Validate(_package.Connections, null, null, null);
return _package;
}
}
}
Hi Brad,
let me have a look – I will get back to you shortly.
Hi,
Thank you for this code. It works well, but I need the code for fixed width as well as delimiter. configuration for column type will get from the database. please let me know how to perform this task.
Hi,
let me see what I can do for you.
I will be in touch.
Regards,
Sifiso