Using SSIS To Populate JET Back End From Flat Files?

[(PeteCresswell)]

Somebody has tasked me with developing SSIS packages to update MS
Access/JET tables from flat files.

Spend the last four days groping around in the SSIS environment.
What a nightmare!!!

Is it just me, or is it overkill to use SSIS instead of MS Access
to perform this kind of task? I feel like I'm trying to kill a
fly with a 12-pound sledge hammer.

Seems to me like, even when somebody is up to speed with SSIS,
the same task will take at least three times more man hours to
accomplish using SSIS than with using MS Access.


??

 

[Lynn Trapp]

Pete,
It may very well depend on how easily you can connect to the flat files from
Access. If you have no problem doing that, then you are probably correct.

 

[(PeteCresswell)]

Per Lynn Trapp:

It may very well depend on how easily you can connect to the flat files from
Access. If you have no problem doing that, then you are probably correct.

I think that's a significant part of it for flat files with many
columns.

With JET, one can concoct a Schema.INI file from the flat file's
documentation pretty quickly - then all, say, 197 fields are
present and accounted for when one does a File | Import or links
to the file.

OTOH with SSIS, one has to explicitly type column names and keep
track of all the field lengths - adding up lengths of unused
columns to create filler between used columns.

I tried replacing the SSIS Flat File Source with an OLE DB Source
that uses the JET provider in hopes of leveraging the Schema.INI
thing.... but it's not buying it.

But even beyond the flat file access, there seems tb so much work
defining every column everywhere - even in error streams - and
every time some object's props are changed errors ripple out to
other objects.

I think I'm starting to rant.....

But it's good not to hear anybody say "Geeze, SSIS is just soooo
simple and quick....."

 

[Lynn Trapp]

--
Lynn Trapp


Ranting is not always a bad thing, Pete. <g> I live by the philosophy in
working with Access and external databases that, if I can use the built in
Access tools to accomplish it, then I will. Don't over turn your wheels using
SSIS if you can get the native Access import (link) to work.

[snip]

 

[Roger Carlson]

Is the flat file a text file (CSV)? The easiest thing is to link the CSV
and use an update query to fill the Access table. Take about 15 minutes to
set up.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[David W. Fenton]

OTOH with SSIS, one has to explicitly type column names and keep
track of all the field lengths - adding up lengths of unused
columns to create filler between used columns.

You could use Access to create an import spec, then use the
MSysIMEXSpecs and MSysIMEXColumns table to write out the
specification in a form that SSIS can use.

 

[(PeteCresswell)]

Per David W. Fenton:

You could use Access to create an import spec, then use the
MSysIMEXSpecs and MSysIMEXColumns table to write out the
specification in a form that SSIS can use.

I'm sort of doing the first part to create the Schema.ini file.

Been looking for a way to get information like that into SSIS for
most of the past week, but no luck.

Anybody know what form of the data SSIS could use?

 

[(PeteCresswell)]

Per Roger Carlson:

Is the flat file a text file (CSV)? The easiest thing is to link the CSV
and use an update query to fill the Access table. Take about 15 minutes to
set up.

That's an example of why SSIS seems tb a *lot* more
labor-intensive.