Hi! I need to produce an Excel file using the output of approximately 25 SP's in SSIS. I am thinking of creating variables of object type and storing the output of execute sql task in it .So in the final DFT how should I proceed with configuring them as sources? Any other suggestions in how to proceed about with this task Thanks a lot
I am no regular user of SSIS but my preference would be to write out each result set to the Excel file as it completes rather than carry it in memory until the end. If the result sets are large and you have 25 of them then I would personally prefer to get that written to disk as soon as possible. I dont think it would be too tricky to built a ForEach loop that runs each procedure in turn and then creates a new sheet in your chosen workbook to store the result set.