Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Capturing unstructured rowsets in SSIS. Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:25 AM
Points: 7, Visits: 186
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
Post #1431151
Posted Friday, March 15, 2013 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:56 AM
Points: 13,343, Visits: 10,206
The dataflow task cannot handle changing metadata. You would need a different dataflow (or source/destination pair) for each type of resultset.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1431531
Posted Friday, March 15, 2013 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:25 AM
Points: 7, Visits: 186
Thanks for the quick reply Koen, that was the conclusion I was coming to from my reading.
Cheers,
Mart
Post #1431576
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse