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.
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.