HI all, I am running an ssis package which will dump the select rows into text files or csv( every 1 hr the job run which will execute ssis package and send the .txt attached in email) here is the code looks like select * from HumanResources.Department --16 rows select * from HumanResources.Employee ---100 rows Select * from HumanResources.EmployeePayHistory --377 rows I tried to keep in Oledb Source t-SQL command then flatfile destination ... But The output i see is of only first 16 rows ... I tried to keep all the above select statements in SP and run an SP but I get the same results the o/p is only first select Statements ... How can i keep all the three o/p s in single text file can anyone let me know .

Do you wan to UNION (ALL) the outputs from all queries? (Columns must be same in all select statements) If the columns you are selecting in all 3 select statements are same then try select * from HumanResources.Department --16 rows UNION ALL select * from HumanResources.Employee ---100 rows UNION ALL Select * from HumanResources.EmployeePayHistory --377 rows

You have to define 3 different oledb source component, one for each table. Or you can have 3 different dataflow task.

In the DataSource Edit ,if you put three select in "SQL Command Text"Area, and click "Preview", the result just get from the first Select Query.

UNION ALL cannot work mostly as the tables are totally different and they have different schema. Three OLEDB tasks would work with OverwriteDestinationData checkbox unchecked on falt file destination.Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

Hi kshatri, Here is a simlar thread, please take it as a reference, Multiple result set to a single text file: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/f41144d9-04a0-4ab1-b74a-3754ae52f1af Thanks, Eileen