How to pass a table of values to a stored procedure from SSIS?

Question

I am really new to SSIS, so I apologize for the "dumb" question. I know open ended "Best Practices" questions are seriously frowned upon, so I will try to be as specific as I can.

I have a basic SSIS package, which moves data from one SQL Database to another SQL Database. There are few data transformations and one filter going on, in the Data Flow task section. I've been trying to come up with a good way to write my data to it's destination, but it can't go straight into the "Final" table (as I would with the Data Flow Destination tasks). I would prefer to pass it as a variable to a stored procedure. I see two options, both of which I don't like:

1. I could use the OLE DB Command and loop through my data line by line, running the stored procedure for each one.
2. I could write the data to a temp table and call the stored procedure from the Control Flow section.

For option 1, it seems like really bad form to have to loop through each data row. Is there no way to call the stored procedure and pass it a table?

I have someone here at work telling me not to use a temp table, so Option 2 seems off the table. I think if I had justification for using Option 2, as a "best practice", I might be able to convince them to let me do the data load that way.

Am I missing something? Is there another way to tackle this problem? If you could include a reason WHY with your answer, it would greatly be appreciated, since I'm trying to understand the rhyme and reason.

EDIT: To clarify, I'm trying to find a good way to pass a table-valued parameter.

Answer 1

Is there no way to call the stored procedure and pass it a table?

There is, if you're using SQL Server 2008 or higher: table-valued parameters (TVPs).

I've already blogged about how to use TVPs in SSIS. Essentially, you have to use ADO.NET inside a Script Component. While this isn't the prettiest solution, it gets the job done relatively cleanly with all the code in one spot.