Capturing unstructured rowsets in SSIS.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Capturing unstructured rowsets in SSIS.

Capturing unstructured rowsets in SSIS.

Rate Topic

Display Mode

Topic Options

Author

Message

mharrison-632391

Posted Thursday, March 14, 2013 11:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 8:18 AM
Points: 3, Visits: 156

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 Smile