Flat File Source -- Max Record Details

[manmaria]

I got few rows in a flat file and want to get the full details for a particular criteria.


Flat file format
Col1(date),col2(char),Col3(char),Col4(Char)

Get col2,col3,col4 for Max(col1).

Can I use the Derived Column Transformation?

Thanks in advance

 

[manmaria]

Got a work around. Not pretty.

1.Get the Max value of Col1
2.Join with the Flat file on col1
3.Get the rest of the Values

Any Other approaches?
Tks

 

[Catadmin]

Not sure what you mean by full details of for a particular criteria. Are you saying you specifically want all the columns for a row where a certain column equals a certain value?

If so, my thoughts are to pull all the table values into a temp table then use an Execute SQL task to do your
"Select *
from MyTempTable
Group By Col1, Col2, Col3, Col4
Having Max(Col1)"

Does that help?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

Sorry, for the delayed response. Your assumption is right about the full details of the record. Rest of the information for col1.

Is that a temp table created temporarily and go away at the end or you are calling that as a temporary table but it is a permanent table unless I deleted at the end of the process. If there is a way to create a temp table for the ETL purpose, I REALLY like to know.

Thanks

 

[Catadmin]

Unless I'm mistaken, you should be able to create a Temp table that "goes away" with an Execute SQL Task. You could do a Select...Into #MyTempTable for example. Or you could create a perm table to be deleted later.

Either way, you need to remember the Drop Table statement. Temporary tables, especially globals (##), can persist if your connection is inadvertantly left open, so it never hurts to add in the Drop Table #MyTempTable into your code just for efficiency's sake.

BTW, I just verified in BOL 2k5 that Temp tables work the same as in SS 2000. I.E., local Temp Tables (#) are available only in the current session while global Temp Tables (##) are available in all sessions, so choose your type depending on what you want to do.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

How to do you insert into a temp table in Exec Sql Task? then use that temp table to ole db destination?

Thanks in advance,

 

[manmaria]

I guess this is the temp table

select col1,col2,col3
into #blah2
from blah1

Here blah1 is created before but not the blah2.

 

[Catadmin]

Your syntax is correct. It's the same as the creation of a Temp Table in Query Analyzer or SSMS. Of course, blah1 has to have been previously created before you can select from it.

You could also use a Create Table #blah2 .... statement then do an Insert...Select... statement to fill the temp table. I find a Select...Into is much less typing, though.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"