2

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.

Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for opening BiML door for me. I was totally unaware of it. As it will take a while for me to try few scripts and understand how BiML works, is there any direct link/script which answers my question? Slightly similar script will also do.
No, unfortunately I don't have a shortcut that directly solves your problem. Just that BiML can lead to a solution.
0

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.

Improve this answer

5 Comments

I'm a novice in C#. There are more than 10 million records and not sure how script behaves with such volume of data. Also, feels scary to throw all this data in a single variable.
@Ash, 10M records in an Object variable and later - parsing in C# script are fine. Problem is with your goal and SSIS as a tool. If you want a generic SP processor which runs SP and stores its result in a file with almost no transformation - then pure C# would be simpler. You can try SQLCMD or BCP utilities from SQL. SSIS shines if you need to transform some fixed layout data; it allows to design majority of data transformations in a visual tool. Your task is much simpler and generic - where SSIS advantage turns into a burden.
Thank you for the reply Ferdipux.
Thank you for the reply Ferdipux. No strict rule to use SSIS and I'm happy if this can be achieved in C#. With my little knowledge in Visual Basic, I tried to combine SSIS with it and produce a output file, but of no avail. Here is what I tried. Execute sql task (store result set in var_File variable) -> Script task (Use var_File variable to generate output file) As I'm keeping babysteps in C#, would you mind sharing piece of C# code for above?
@Ash, you can write Script task with Visual Basic, it can be chosen at design time, see ScriptLanguage dropdown of Script task.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.