Earlier, I was running many sql query and was coping the eaxtract in csv file manually. Now, I am trying to make a ssis package which does the same.
I have used OLE DB Source in for-each loop to do this. But thats not working. So am i doing right? or is there any thing else which can help me to do(Other than the script task)?
SSIS is not exactly your tool, and I will try to explain SSIS approach to data transformation.
SSIS as a service runs SSIS packages. SSIS package extracts data from a source and writes it to a destination defined in the package. At the package design time, you set metadata on the data extracted and stored - column names and data type. On the run, SSIS validates source and destination against metadata defined in the package at design time, and fires error if metadata does not match.
In your case, set of columns from the source and destination can vary significantly. Either you have to create a SSIS package for each 'source-destination' pair (and BIML can simplify this job), or switch to other tool. SSIS cannot handle arbitrary data source and destination without first defining its metadata at package design time.