There was a scenario, where I need to transfer some data as the result of the complex query to a destination table. As the query was complex, so I was using table variable instead of temporary table like #tmptable1 etc. Although unlike table variable, we can’t create #temp table in the oledb source of SSIS. If we use temporary table like #tmptable, it shows invalid object #tmptable.
Well coming to the point, I created the below query using table variable @table1. I was using this query in the OleDb source of SSIS package as shown below .
I also created one destination table as shown below, which I will use as the destination for oledb source query.
When executed the package, it executed successfully. But in the data flow path it was not showing the number of rows returned from source. When checked in the destination table EmpDptInfo, It was empty.
I again executed the package and it was executed successfully along with result returning from source to destination.
Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.