generic ssis package to create output text files using multiple procedures

Question

I've few procedures(sql server) which produce output and I manually save them in text files with headers.

I need a generic SSIS package which takes procedure_name, output_filename as inputs and produce ouput text file.

As different procedure produces different columns, I need to handle metadata dynamically.

Ex: proc1 produces following result

col1|col2

a|b

proc2 produces following result:

col1|col2|col3

1|2|a

One solution was to change all the procedures to produce output as a single row, thus SSIS treats it as one column and produces output file. Ex:

'col1|col2|cole' AS rowdata

'1|2|a' AS rowdata

However, there are quite a few procedures and changing every procedure will take lot of time and might end-up in human errors.

So, I'd like to know, if there is a way in SSIS, where I could handle metadata dynamically and produce output file.

Answer 1

You can look into BiML, which uses meta data to dynamically build and then execute packages.

Answer 2

Well, it is hardly possible in traditional SSIS package, where all outputs are defined at design time and validated at runtime. Altering its number leads to validation errors and stops package execution.
You can play a trick with SQL Execute Task and Script Component. Exec SQL runs SP and returns SP result as 'Full Result Set' stored in an Object variable. Then at Script task you open this variable which is in fact an ADO recordset; it can be read by the System.Data.OleDb.OleDbDataAdapter. For example:

// Set up the DataAdapter to extract the data,  
// and the DataTable object to capture those results  
OleDbDataAdapter da = OleDbDataAdapter(); 
DataTable dt = DataTable();
// Extract the data from the object variable into the table 
da.Fill(dt, Variables.vResults);  

Then you can work with DataSet and flatten into single variable for later use. Or - create and save file in C# script directly.