I have a need to do some processing of a set of records in a component script task in SSIS 2005. Of course this means VB and no C#. I can live with that as I have no problem going back and forth between the two.
What I do have a problem with is dealing with data out of my comfort zone - my comfort zone being business objects that provide a layer of abstraction where I can process data much as I would in VFP.
Now I have to do something else - though I'm not sure the best approach.
I get a set of records from a CSV feed and create rows in a SQL2005 table ( Staging ) . The set of records has a batchid which identifies them.
The CSV populates about half of the columns in the row. The others need to be filled in based on characteristics of the other columns in the row, lookups against other tables, or, in some cases, their position as part of aggregates. ( the feed source basically flat-files what is going to move from this staging table to a relational model.
In VFP or .NET with my Strataframe business objects I could do this in my sleep. But suddenly I am wondering about the best approach.
First, I need to look at the SYMBOL column and be sure it exists in another table. If the MULTI bit column in the lookup table is 1 I need to set a flag in the Staging table COMBINED column to 1.
I also need to validate a combination of two columns against another lookup table.
Then I need to conditionally fill in other columns with values columns in the same row populated by the import ( this usually involves changing data type from varchar to decimal or int - and of course trapping for errors if the cast doesn't work)
Another complication is that for some reason in the BIS IDE the Visualizer for datatables and datasets refuses to appear ( no mag glass ) and I tried some 3rd party visualizers, also with no success )
So, should I be thinking sql tempdb, set based, SP or ADO datatables or what else could I do?
I realize this a vague and possibly stupid question but since I have asked an answered many of those here I have hope
TIA
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
