>>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>