1

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.

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

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.

| improve this answer | |
  • 1
    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 '14 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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