Store Procedure Result to Text file using SSIS package

Question

Here i am new in Developing the SSIS package

I need your support to come up with the solution.

I have 10 different set of stored procedures which I have to export into text file, all 10 procedures will return the same set of columns (only calling parameters are different).

I am not getting the solution how to do ?

Could you please help me to understand how to export the data from a stored procedure output to tab delimited text file?

Please let me know how to build the ssis package ?

Thanks

Answer 1

This is very hard to do without putting pictures in on each step. I do not seem to be able to put pictures in so I will try to describe in is much detail as I can.

You have to first set up a connection to the database where you are going to run the stored procedures from. This means creating a connection manager for "New OLE DB Connection". You will need vaild login to the database information to create this connection.

In the control area I would set up a "Execute SQL Task". I would set the result set to full result set and set the connection to the one you named in the prior step. To call a stored procedure from a SQL task use something like "exec ? = dbo.usp_check_load_table_all @JobCode = ?, @TransId = ? , @Status = ?, @TurnStatusOff = ?" The first ? is the return code from the stored procedure. The others are the parameters to run the stored procedure. Now you are running 10 different stored procedures and I only know how to run one but you could create ten packages, one to run each and concatenate the files when it is done. In the parameter mapping you set the values for the variables to run with. Make sure to create a USER::ReturnValue type long for the return code. The results set needs one entry a USER::Results of type object.

You now put in a foreach loop for a ADO enumerator putting in the USER::Results in as the variable. This allows you to read in each row one at a time. You must create user variables for the variable mapping to go into.

I would then do a data flow task and put a derived column task and set up each of the fields you want to write to the file from the USER:: fieids you created for the foreach loop.

I would create a flat file connection in the connection manager as a delimited file, tab delimited. You will need a file that looks like the output you desire as you will need to map each field in the file.

Add a flat file destination to under the deriried column task and map it to the flat file you just created. Now map each field to the output.

I hope this is helpful as I was once new SSIS myself.