June 2, 2011 at 10:24 am
Has anyone tried executing multiple sql statements in SSIS and get the result sets in either as xls or txt file. any help on this will be greatly appreciated. Thanks
June 2, 2011 at 10:35 am
No problem - but you'll need a dataflow for each one.
You can't easily do this dynamically, if that's what you're thinking of.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 2, 2011 at 10:54 am
Thanks. But calll all the results i get from each sqls statement be appendeded to one file. What i trying is to generate a report which gets counts from different tables and stores it in txt/xls file . with each dataflow wont it overwrite the previous file?
June 2, 2011 at 11:57 am
You can do this with multiple Execute SQL tasks, or with full DataFlows. In either case you can set them up so that the first file inserts, and subsequent files update.
June 2, 2011 at 12:02 pm
You could accomplish this with several dataflows outputing to temptables or a single temptable and then perform one last dataflow where it unions those tables and outputs to a single file.
Several different ways to accomplish this.
Or you could have a single data flow that calls a stored procedure that performs all of the aggregation, count etc of the different tables and then outputs that to a single file.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 2, 2011 at 12:21 pm
Or use multiple OLEDB sources to call each proc/database/whatnot, and then use the union operator (it doesn't stall the stream) to a single delivery point.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 14, 2011 at 7:07 am
Something I've done in the past is nested for each loops. We frequently need these types of reports, or we need to search SPROC definitions for certain key words (or table names).
We use the the first for each loop to set the names of the servers to loop through. Then the second loop will use the ADO objects to pull back the names of the databases on that server. Once we have that, we update the "source" connections via expressions to point to the current server and database to execute the query and write the results to the Excel or Text file.
There is an option to append the rows rather than overwrite so that the end results is a full list of whatever you wanted. The caveat here is that your sql statement output must be exactly the same (i.e. column names and datatypes).
The end result of our package basically ends up being 2 nested For Each loops with a single DataFlowTask; however, we have about 6 connections to handle all the needs of the loops, etc. It's pretty complicated to setup, but in the long run, it's been worth it since we can make minor updates to the SQL statement and the destination file and generate very useful information in a matter of minutes.
Our support team uses this package all the time for various items.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply