SSIS Custom Source Task - Read a text file

Question

SSIS Custom Source Task - Read a text file

tim old-261622

Grasshopper

Points: 23
More actions
July 16, 2009 at 2:52 am

#212447

Hi
I am processing a text file with the like the following:
IP|string|25,Date|datetime|0,Num|int|0,LogName|string|50
192.168.114.201, 12345, 7, W3SVC2
192.168.115.201, 12345, 7, W3SVC3
When I build the output columns for eg; outputColumn.SetDataTypeProperties(intDataType, intLength, 0, 0, 0); I build the datatype on a simple switch statement:
protected DataType ConvertManagedTypeToBufferType(string managedDataType)
{
DataType dataType = new DataType();
switch (managedDataType)
{
case "string":
dataType = DataType.DT_WSTR;
break;
case "datetime":
//Not supported in pipeline buffer
dataType = DataType.DT_WSTR;
break;
case "int":
dataType = DataType.DT_I2;
break;
default:
dataType = DataType.DT_WSTR;
break;
}
return dataType;
}
And then the buffer gets processed here at runtime:
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
PipelineBuffer buffer = buffers[0];
int noOfRowsToRead = 1;
for (int i = 0; i <=noOfRowsToRead; i++)
{
//Read predetermined number of rows.
if (i == 0)
{
//Read first line with columns - do nothing we have already built schema
textReader.ReadLine();
}
else
{
//Read subsequent lines
string line = textReader.ReadLine();
if (line != null)
{
//Split into delimetred array.
string[] column = line.Split(',');
buffer.AddRow();
//Loop column[] array and add source into output buffer
for (int x = 0; x <= column.Count() - 1; x++)
{
buffer.SetString(x, column[x].ToString());
}
}
}
}
buffer.SetEndOfRowset();
}
I get a DT_DBDATE error on the date field, so I assume I should use relevant buffer.SetDateTime / buffer.SetInt etc...
Has anyone been here before, I just think there must be a simpler solution.
Thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Whoa, what is that!? 😀

I'm assuming that 12345 is just dummy data and not some obscure representation of a date (number of days since a blue moon, or whatever?)

I would think that you could use a standard flat-file source to read that data in, with everything as a string except for the integer.

Then process your strange date column somehow to get it into SQL Server format - probably using a derived column or, if necessary, a Script Component.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

Yes, my feeling is like Phil's - why are you not using the data flow toolbox components, like flat file source, or data conversion, etc? Unless I'm missing something, these should be more than adequate to parse such a simple csv file. The only time I have had to resort to code is when I've got a really strange file format to work with, or more complex processing that has to occur.

If you're output is a SQL table you might try using the Import feature in Management Studio and save the SSIS package it creates to give you an example of a Data Flow using the tollbox components.