SSIS how to pass Table-Value parameter into stored procedure

Question

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.

How to pass Table-Value parameter into ADO.NET Source.
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

If you are using SQL Server 2017, you can send JSON directly, it will save creating table type for each stored procedure — Pranav Singh
– Pranav Singh, Commented Sep 24, 2018 at 10:26
Are you asking, How to pass value from c# since you know how to use table valued parameter in stored proc? — Pranav Singh
– Pranav Singh, Commented Sep 24, 2018 at 10:27
It is question about SSIS and calling Spc inside SSIS, and currently it seems can be only done in script C# component, which from my perspective over complicated. — justromagod
– justromagod, Commented Sep 24, 2018 at 11:16

userfl89 · Accepted Answer · 2018-09-24 15:52:19Z

2

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

answered Sep 24, 2018 at 15:52

userfl89

4,8201 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

justromagod Over a year ago

It worked for me. Thank you for help. It is even possible to create Expression Task and build this SQL.

justromagod Over a year ago

stackoverflow.com/questions/20093362/… => ADO.NET dynamic SQL

Collectives™ on Stack Overflow

SSIS how to pass Table-Value parameter into stored procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related