SSIS: How to use a query containing table variables for an OLE DB Source

For a more recent version, please visit: http://smehrozalam.wordpress.com/2008/11/06/ssis-how-to-use-a-query-containing-table-variables-for-an-ole-db-source/

This one consumed a lot of my brain energies yesterday. I was working on some Sql Server Integration Services task and needed to create a complex query containing table variables. The query was working fine in SQL Management Studio and the SSIS designer was even previewing the data correctly. But when the task was actually executed, the query did not return any rows. I posted a question on TechNet forums and got an answer from Charles Talleyrand here. Actually, I needed a SET NOCOUNT ON statement. Let me explain using an example:

Say, I want to create a table variable (not a temp table), populate it and then use the result set as an input for an OLE DB Source. Here's a sample query for this:


declare @mytable table
(
col1 int,
col2 varchar(20)
)

insert @mytable values (1, 'one')
insert @mytable values (2, 'two')
insert @mytable values (3, 'three')

select * from @mytable


The SSIS designer will correctly identify the column names from the above query and even display the data if you click on the Preview button. But when the task is executed, no rows will be returned. I think the output of insert statements: i.e. 1 row(s) affected was the source of problem and so a SET NOCOUNT ON prevented such interferences. Hooray!! So if you are ever working on some SSIS Data Transfer Task and want to use table variables inside your query, make sure you do not forget the SET NOCOUNT ON statement.