blogs.conchango.com

Jamie,

Looks great! Another one to put in my bag :) Ofcourse, I always wear my performance hat, so perhaps a small adjustment could improve it. Currently, for every column, the array-split function is called. If there are say 20 columns and 5 million rows, that would be rather costly.
If you call the tokenize function once per row and have it return the array, you could apply the values subsequently. Something like:

   tokenArray = Tokenize(input, 2)
   Row.Column1 = tokenArray[0]
   Row.Column2 = tokenArray[1]

I would expect it to be quicker.

Excellent tip, quite helpful. Pity it wasn't built into the system.

I think the Tokenise function should be changed slightly to incorporate the delimiter paramter:

e.g.

from:

tokenArray = input.Split(";".ToCharArray)

to:    

tokenArray = input.Split(delimiter.ToCharArray)

Hi

Is there any other way to refer to the columns besides the column names , say Row.Item(0) or something?

Good script I tried it and it works quite well with 5 to 10 columns.

I have 51 columns and having buffer overflow on Row.Column0  (Row.Column0   or Line is a String )

Regarding  referring to the columns besides the column names ..The example was very helpful (I used IDTSOutputColumn90 instead),  but the col.item(i) is read only..

I am trying to also assign value to the output columns, reffering to them by index. Is there a way ?

Thanks

Hi,

In SSIS,Is there any way of parsing a flat file with repeating headers?

Like,Can i ignore some x no.of rows and pass  y no. of rows,by putting my logic in a loop?

Hi, maybe you can help me.

I'm looking for a way to reference columns of a row using a variable.

What I'm trying to accomplish is a function in a script transformation that can write an output _row_ for each input _column_. To this end I think I can reuse the code above, which iterates through columns names and, for each row, find a way to use column names like this:

   [....]    

   For Each col In cols

          if Row.[col.name] <> "string" then 'Access the value of the current column for the current row

             outputbuffer.addrow()

             outputbuffer.attribute = col.name

             outputbuffer.value = Row.[col.name]

          end if

      Next

In another script i have the same problem but with the outputbuffer columns, I would like to access the outputbuffer columns using variables, i.e. calling an output column by name (outputbuffer.[stringwithcolumnname] = value).

Is it possible?

Thanks,

Paulo

Thanks a lot Jamie!!

Best regards

I have a slightly different problem with quoted delimited columns where some of the columns include the delimiter character, e.g.

Date,Action,Status,UserID,UserFullName

"20080414","Return VALM to","DEAD","KRISHNA1","Krishnana, Anuradha"

"20080414","Return VALM to","DEAD","GOODWIN","Goodwin, Noel"      

"20080414","Resend to Opera","DEAD","BELLG1","Bell, Gavin"

These imported okay in DTS and also preview okay in SSIS when configured with  {CR}{LF} Row Delimiter, {CR}{LF} as Column Delimiter for UserFullname column and TextQualifer as ".

When I run the package it then fails

Thanks, Jamie.  That's cool..

Mark OMeara,

'If you call the tokenize function once per row and have it return the array, you could apply the values subsequently. Something like:

  tokenArray = Tokenize(input, 2)

  Row.Column1 = tokenArray[0]

  Row.Column2 = tokenArray[1]

I would expect it to be quicker.'

it will be quicker, but it will lost the protection against a request for a token that doesn't exist.

Hi,

I just tried your solution and while the logic works OK, the process keeps failing due to running out of memory, presumably because the script function is writing everything out to memory.

[FF_SRC Input File [643]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Is there a way to make the script write each row directly out to the DB destination, rather than storing it all in memory?

Hey, gr8 article, i having a similar problem but i want to export/create a unstructured flat file , any idea how i can achieve this..

flat file looks like this

a,b,123,34,d,

12,23,34,1,

w,z,

a

Any idea will be greatly appreciated

PingBack from http://www.lifewithsqlserver.com/2008/10/another-ssis-gotcha

How would you go about DELETING the flat file after it is imported?

any help would be great. Thanks.

I'm using a tab delimited flat file and get the error that the Value is too large for the column data area of the buffer.  Works fine for mini files but anything over a hundred records I get this error.

Is there a way to increase the buffer size?