Saturday, April 27, 2013

SSIS : Using table variable in OLEdb source in data flow task doesn’t return rows to destination



Using table variable in OLEdb source in data flow task doesn’t return rows to destination

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.







Then I tried to use ‘SET NOCOUNT ON’ before all the queries in oledb source, which prevents it to return the number of rows.  When SET NOCOUNT is OFF, the count is returned.

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.

6 comments:

  1. Great post. Very useful.

    ReplyDelete
  2. I was wondering why the source component didn't produce rows when the preview did. Now I know :-)

    ReplyDelete
  3. Thanks. Solved my problem. But I am using a #temp table.

    ReplyDelete
  4. good post. solved my problem.

    ReplyDelete