March 14, 2013 at 11:23 am
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 🙂
Mart
March 15, 2013 at 7:35 am
The dataflow task cannot handle changing metadata. You would need a different dataflow (or source/destination pair) for each type of resultset.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 15, 2013 at 8:58 am
Thanks for the quick reply Koen, that was the conclusion I was coming to from my reading.
Cheers,
Mart
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply