Complicated flat file needs

[groleau]

I have successfully loaded these files into Access and SQL Server 2000 (via DTS) but I first used VBA to reformat them.

The reformatting adds a HUGE amount of time to the loading.

Here are some of the reasons I used a preprocessing step with DTS. I am hoping SSIS has some good ways to handle these issues:

1. The row delimiter has to be determined by looking at
the 105th byte of the file. The first record has
only fixed width fields, and the last field in this
first record is followed by this delimiter. Many
characters are legal delimiters.

2. This record delimiter may or may not be followed by
a line break.

3. The column (field) delimiter is also determined by
examining a fixed location in the first record.

4. The number of fields per record varies. I pick some
number guaranteed to always be enough, and then want
the extras to be null. (DTS Bulk Insert ignored the
row delimiter--when I pretended it was fixed--and
loaded the missing fields from the beginning of the
next record!)

Is there a way to load the data from a string instead
of a file? I could convert my preprocessor into a
function returning the entire modified file contents.
That would still be very slow, but not as slow as rewriting
the modified file.

--
Wes Groleau

http://www.Parkview.com/

 

[MDXer]

You could use a Script task and designate it as a source task. Then in the Script you would have to manually create your output columns and then parse the data based on the rules of your data and specify what pieces belonged to which output column. Kind of time consumming and tedious but yes it is possible.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[groleau]

That's not much different from what I did before with Access and DTS. VERY slow--hour and a half to load a week's data.

Easier to load the data into an ADO.Net object and transfer to SQL Server. Not faster, just easier. (Easier, because the pre-processing I used before is 75% of the code to do that.

--
Wes Groleau

http://www.Parkview.com/

 

[katbear]

check this out:

http://agilebi.com/cs/blogs/jwelch/...at-files-with-varying-numbers-of-columns.aspx

 

[groleau]

Thanks, katbear.

I didn't really have time to read it today, but it looks promising

--
Wes Groleau

http://www.Parkview.com/

 

[katbear]

It gives a general idea anyways, how to handle irregular flat files.

SSIS is supposed to be A LOT faster than DTS, so maybe you'll have more luck, speed-wise.