0

I need to read data from a DB1 and write them to another DB2. I use a complex query with CTEs and temp tables and no, i can't put this query in a SProc. I use an OLE DB source and an OLE DB Destination.

When i put the query as SQL Command in the OLEDBSource I get the usual complaint about not being able to determine metadata because a CTE is using a temp table.

I can't use the "with result sets" workaround because it is not a SProc. So i try with the other workaround, the "SET FMTONLY ON/OFF" .

Now the OLE DB Source accepts my query but it outputs two datasets, the first empty and the second is the data I need. The OLE DB Destination doesn't write a single row because it is reading only the first resultset, the empty one.

How can i solve this?

I cannot change the temp tables in something else and basically i can't change the query. I am looking for a SSIS solution if possible, not a SQL solution. Thx.

Johannes Wentu
  • 931
  • 1
  • 14
  • 28

1 Answers1

0

For an SSIS solution, you cannot use an OLE DB Source. That component can only access the first result set.

What you can do is use a Script Transformation as your data source, and access the second result set in the usual way, and send its columns to the output of the script.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I am not expert enough in SSIS to understand what you wrote. Do you mean that i have to use a Script Transformation (ST) INSTEAD of the OLEDBSource and write in this ST a C# code that actually goes on the DB to retrieve data, put in in a dataset that will have 2 tables and then take the second table and send it to the OLEDB destination? – Johannes Wentu Sep 02 '16 at 09:13
  • Yes, that's exactly what I mean. – Tab Alleman Sep 02 '16 at 12:59