Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 sproc for each one.
  2. I could write the data to a temp table and call the sproc 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 sproc 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.

share|improve this question
    
How big might the table be that you are going to try and pass? –  Edward Dortland Jan 2 at 23:54
    
@Edward - somewhere between 50 and 200 rows by 8 columns, very small table. –  Macromika Jan 3 at 14:49

1 Answer 1

Is there no way to call the sproc 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.

share|improve this answer
    
Hmmm... the code looks straightforward enough. I'm just surprised that I have to write custom code and that they don't have any built-in functionality to do it for me. –  Macromika Jan 3 at 14:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.