I am an SSIS newbie. I have a TSQL batch script that that I run manually in SSMS 2008R2 against multiple remote servers, I save the results as tab delimited files. The batch executes multiple stored procs, dynamic SQL and simple queries. The results contain many different row sets and column formats. I do not need to process the results once they have been captured.
I want to automate the script execution in SSIS and capture the result set, regardless of the variation in column structure, on the central server. Is this feasible please and if so, what is the best data flow task to use?
I have prototyped the supporting SSIS framework for dynamic connections etc so the problem is just the correct way to execute and capture the batch results. I am open to all suggestions for the data flow task, flat file, Excel or a SQL Server table containing the results.
Creating a flat file on the remote server and using file copy or ftp to the central server is not an option. I would prefer not to deploy the query batch as a stored procedure on each remote server.
Many thanks for your help and yes, I am sorry if this is painfully obvious :-)