Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Complicated flat file needs

Complicated flat file needs

Complicated flat file needs

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

RE: Complicated flat file needs

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.

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

RE: Complicated flat file needs

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

RE: Complicated flat file needs

Thanks, katbear.

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

Wes Groleau

RE: Complicated flat file needs

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close