OUTPUT of OLEDBC Command to another OLEDB comand

Kuldeep Chitrakar 1 Reputation point
2021-11-17T08:47:37.47+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,857 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CarrinWu-MSFT 6,796 Reputation points Microsoft Employee
    2021-11-18T09:50:46.647+00:00

    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.

    No comments