CODEWITHSUNDEEP
c#sql-serverssistable-valued-parameters
justromagod
justromagod

Reputation: 993

SSIS how to pass Table-Value parameter into stored procedure

There are multiple articles in internet about passing Table-Value parameter in SSIS, but I am wondering that they are all very old and wrong as over complicated, because they want script components or a lot of other things to be done.

So questions are.

  1. How to pass Table-Value parameter into ADO.NET Source.
  2. How to pass Table-Value parameter into OLE DB Source.

How to do it best efficient and quickest way

Let's assume Table Value Parameter defined as Type

CREATE TYPE [dbo].[IDs_TVP] AS TABLE(
    [ID] [INT] NOT NULL
)

and procedure can be called

DECLARE @ID dbo.IDs_TVP
INSERT INTO @Clients
(
    Text
)
VALUES 
('1'),
('1'),
('3')

EXEC dbo.GetClients  @Clients = @Clients

and procedure defined

CREATE PROCEDURE [dbo].[GetClients]

    @Clients dbo.IDs_TVP READONLY
AS

SELECT * FROM @Clients

Upvotes: 1

Views: 3488

Answers (1)

userfl89
userfl89

Reputation: 4810

I'm not sure how much less verbose this option would be than a script task, but if you need use a table valued parameter to pass data into a into a stored procedure, after working with this data in multiple components in the package, you could load the data to a global temp table to do whatever processing is necessary. Once you're ready to send the data into the stored procedure using the table valued parameter, populate a T-SQL variable of the same type as the TVP and execute the stored procedure with this variable as the TVP parameter. In order to do this, make sure to place all the related tasks in a Sequence Container that has the TransactionOption set to Required. The components can either have their TransactionOption at Supported or set to Required as well. If all the components in the package are related to this specific objective/data, the Sequence container can be omitted and the same TransactionOption setting would apply to the package instead. If this approach works for you, you may want to consider removing the TVP from the stored procedure and just using the temp table. Below is an example of an Execute SQL Task that populates a table type variable from the temp table, then executes the stored procedure with this as a parameter. This will work with both OLE DB and ADO.NET connections

DECLARE @YourParameter AS IDs_TVP

INSERT INTO @YourParameter (ID)
SELECT ID FROM ##TempTable

EXEC dbo.TestSP @YourParameter

DROP TABLE ##TempTable

Upvotes: 2

Related Questions