c# - How to insert result set stored in SSIS variable into a table in SQL Server?

Question

I have to take a result set generated from a query in a database in one server, and insert that into a table in another database in another server. I can't do this using a Linked Server as the DBA does not have it set up and will not set it up either. So I'm forced to use SSIS to do this. I have gotten to the point where I am storing the result set generated from the query into an object variable. This is the query to get the result set below.

SELECT a.*
FROM dbo.SFprod_Application a
WHERE RECORDTYPEID = '0122L000000kDXZQA2'
ORDER BY ETL_ID DESC;

I have the data generated from that query in a variable with Result Name 0 and Variable Name User::LoadApplication.

I am unsure how to take the result set that is stored in this variable, and insert it into a table in SQL Server. For example I would like to take the results stored in that variable and insert into a table called dbo.FApplication that is in database A1 in server A2.

The destination table is configured with the correct columns and data types needed to store the result set. I just don't know how to get that from the variable into the target table. I have seen some mention of using for each loop containers and maybe C# code. I am fairly new to SSIS, so I'm not very familiar with C# or foreach containers. Any help showing how to use those properly would be appreciated.

question from:https://stackoverflow.com/questions/65879852/how-to-insert-result-set-stored-in-ssis-variable-into-a-table-in-sql-server

Answer 1

Waitting for answers