>>>>>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
>>>>
>>>>Well, I would do this in T-SQL code but it's because I am familiar with T-SQL and less familiar with SSIS. I suggest to ask your question in SSIS forum at MSDN as you have a broader audience there. If you do, please post a link here.
>>>>
>>>>Thanks.
>>>
>>>SSIS 2005 is just VB .NET 2005 so AFAIK it thinks ADO in the scripting language.
>>>
>>>Doing it is TSQL would certainly be an option in that I could use SPs to do what needed to be done to the set on the backend and call it from within SSIS no problem.
>>>
>>>Of course that would mean being a lot better at TSQL than I am now. Sure glad I have some smart people I know who I a can ask questions <g>
>>>
>>>Actually, as I think about it, SPs wouldn't be a bad idea in that I really do have more resources both on line and at DJ who really know how to do it that way. and I can write and test it all in SSMS and see what is going on. I really fear getting off into the world of ADO which at the metal level has always been a clunky data access method without some really good higher level framework stuff wrapped around it ( thank god for EF and Linq )
>>>
>>>Prepare for a lot of dumb questions <bg>
>>
>>Hi Charles
>>
>>I've been doing something similar (loading data from csv files) again with lots of lookups and logic and dependencies. I've gone with an SSIS package controlling
>>FTPing in the data ,
>> a data flow step to load the csv file into a tempdb table
>>calls to stored procedures to clean and handle the data
>>and wrapping the processing of each line of the csv in a transaction
>>The csv ends up each row inserting data into many tables.
>
>Thanks. In my case the load from csv to sql was the easy part - of course I simplified it by making the first n columns of my staging table an exact varchar match for what I could expect from the CSV, did not import nulls and have default values on every column in the table.
>
>At that point the CSV gets archived and I am working with a SQL Server table.
>
> Have decided to not even consider processing the derived column values and some of the lookups etc that has a lot of business logic wrapped around it with any kind of ADO (and there is the thing about the visualizers being non-functional in my BIS ) or trying to get procedural with VB ( this is 2005 ) so the only thing a script task might do is call an SP and I am more inclined to use ExecuteSQL tasks.
>
>I needed an excuse to improve my set based TSQL chops on a big clients dime so this looks like the ideal place and since they smile on back end processing and there are a lot of SQL gurus on staff to help me should be a lot of fun.
>
>In C# I always use either Strataframe which has a terrific business object framework to handle data or now I am moving to MVVM / SOA / EF so this is something new for me.
>
>But it never hurts to know how to write TSQL and I have to admit the more I learn the more I warm up to it.
>
>Have you rented out your place in London for the summer and gone to Provence or Costa Brava to work remotely yet?

Strangely for the first time for many years I'm working onsite in London and cycling there very day. 13.5 miles there so 27 miles a day. Enjoying the cycling so much through metropolitan traffic its making me not want to give the job up :-) . Though I have discovered that cycling in heavy hail is extremely painful.