OUTPUT of OLEDBC Command to another OLEDB comand

Question

Hi

I am using OLEDB command to dynamically generating INSERT Statements which I would like to pass to another OLDBC command to execute. However I am not able to find a option to pass the OUTPUT of OLEDB command to another OLEDB COMMAND in SSIS 2019.

Is there another any way/alternative.

FIRST OLEDB Command will be execute in SQL Server to generate the INSERT and then that will be passed to another OLDBC commend which will execute it on DB2.

TEST TABLES

---

CREATE TABLE dbo.TEST1 ( ID INT, NAME VARCHAR(10))
INSERT INTO dbo.TEST1 values (1,'TEST1')

CREATE TABLE TEST2 ( ID1 INT, NAME1 VARCHAR(10))

OLEDB COMMAND

DECLARE @SqlInsertStr nvarchar(4000)
DECLARE @SqlStr nvarchar(4000)
DECLARE @INSERT_STR nvarchar(4000)

SET @SqlInsertStr = '''INSERT INTO dbo.TEST2 (ID1,NAME1) VALUES (''+ID+'',''''''+CDDESC+'''''')'''
SET @SqlStr = N'select @INSERT_STR='+@SqlInsertStr+' FROM dbo.TEST1'

print @SqlStr

EXEC sp_executesql @SqlStr,N'@INSERT_STR nvarchar(4000) OUTPUT' , @INSERT_STR = @INSERT_STR OUTPUT WITH RESULT SETS ((INSERT_STR nvarchar(4000)));

select @INSERT_STR

Answer 1

Hi @Kuldeep Chitrakar ,

Thanks for your posting.

Please refer to this blog to get detail steps that descript how to use the OLE DB Command task to execute a statement on each row set by set and to get the desired result after manipulation.

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.