How to map the output of the stored procedure to variable in SQL Task?

Question

In SSIS Package, I am executing the stored procedure usp_GetResult which returns 2 rows ( select PathName, FolderPath from config ).

PathName   : InboundFolderPath
FolderPath : c:\Inbound

PathName   : OutboundFolderPath
FolderPath : c:\Outbound

I will have to map the variables @InboundFolderPath = c:\Inbound, @OutboundFolderPath = c:\Outbound

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jan 7, 2019 at 20:09

Tab Alleman · Accepted Answer · 2019-01-07 20:05:04Z

1

The only way I know to do this is to use "Full Result Set" in the Execute SQL Task, and store the result in an object variable.

Then use a Script Task to convert the Object Variable to a Dataset, and iterate through it to populate your scalar variables.

answered Jan 7, 2019 at 20:05

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

Comments

KeithL · Accepted Answer · 2019-01-07 21:35:58Z

1

I haven't really tried this but how about:

Declare @T Table (pathName varchar(100), folderName varchar(100))
Insert @T Exec StoredProc params  

Select max(case when PathName = 'InboundFolderPath' then folderName end ) inBound
    ,max(case when PathName = 'OutboundFolderPath' then folderName end ) outBound
from @T

And then map

answered Jan 7, 2019 at 21:35

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to map the output of the stored procedure to variable in SQL Task?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related