ISD job to load flat file to a table

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Novak
Participant
Posts: 97
Joined: Mon May 21, 2007 10:08 pm
Location: Australia

ISD job to load flat file to a table

Post by Novak »

Hi experts,

Looking for a good idea on how to load any flat file to SQLServer DB.
We have plenty of flat files and some of them can be pretty large. The owners of these files would like test loading them into DB on an ad-hoc basis for exploratory purposes. The database would give them better performances and sharing capabilities.
Could there be a job wrapped into a service that would need a few parameter values provided by the end-users:

1. File name
2. Target table name
3. Table action - append, truncate, create
4. Delimiter character

The job would require:

1. Source file in the pre-determined directory and delimited with a character specified in the parameter above

Is there a way for a job to read this file without record schema? Possibly with column export using delimiter character specified in the parameters, that would then build the character schema to be used by sequential file stage?
Or similar?
Just trying to simplify for the business users that know some SQL but none of DataStage.

Regards,

Novak
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Okay... lots of thoughts flying around in my head right now. Let's see if we can shepherd them out in some kind of a coherent fashion.

High level, I'd be shocked if anyone could put together a single job that could load "any flat file to any table". Sure, you could consume any file as one long string and then restructured it using a Column Import stage but you would need to "supply an import table definition to specify the target columns and their types". So not sure how that attempt would play out. The other thing is something that may have changed as my experience is from many years ago but ISD jobs working with flat files are problematic. Meaning, from what I recall if you open on if is only closed when the service stops. We ended up using them in our services only for static reference data.

Now, please clarify the scope for us. How many target tables are there? What I would consider is a job for each target table, consuming an appropriately formatted flat file. Based on the name of the target table in your ISD request, the service could launch the appropriate job to load that target table. I would also stick with a single required delimiter (perhaps something like a pipe) rather than let them pick one willy-nilly. Hmmm... just noticed that your table options including create, so it would seem the expectation is that they would also be able to create any table they wanted - is that true? If that's the case and that is really what is "needed" here, then the suggestion above assuming an agreed on list of target tables goes right out the window. [sigh]

And that leaves me with a suggestion to forget about using DataStage for this. Give your users who "know some SQL" access to a tool like Toad and their own schemas (or whatever SQLServer calls them) somewhere safe where they have the grants to manage their own tables. Toad can walk a user through reading a flat file, building the appropriate control file to parse it and then loading it into a target table - even creating it in the process. All without needing to know any SQL. Bam! Then you could get back to whatever else you were working on before this came up. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

The first thing that popped into my mind was to use a Document Database, maybe something like MongoDB. I have not used them myself yet but have read about them enough to think that's what they're designed for... just cram miscellaneous stuff in there.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Trending!

#JustCramMiscellaneousStuffInThere

:wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Novak
Participant
Posts: 97
Joined: Mon May 21, 2007 10:08 pm
Location: Australia

Post by Novak »

Thank you both.

Craig, you've reminded me of how simple this can be. We can't have Toad but I have checked quickly with SSMS and can do similar (need to resolve character set defaults).

The scope, roughly, is that about 10 users would load about 2 files per month, and doing so without involving us (IT). It would be pretty good if we gave them this self-service, with some rules in place (e.g. comma delimited files, etc.)
Still wanting to explore using DataStage for this...
How about taking a long string through a loop in transformer stage to be taken apart into columns?
It does not have to be ISD. We can just give them access to Operations Console...
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Glad I could help. Somewhat. :wink:

Be careful with using comma delimited fields if there is any possibility of there being commas in your data. Not sure how much I can help going forward other than to say you certainly could use a transformer loop to chop a string up by its delimiters, the problem is going to be... then what?

In the meantime, if you do end up trying something, let us know how it goes! Specific problems always get more specific answers. :D
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

If you're using Connector stage types, of course, the field delimiter property can be the value of a job parameter.

We've just been making use of that bulk loading data into Snowflake.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply