Thursday, November 06, 2008

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.

Labels: , ,

7 Comments:

At December 19, 2008 at 9:26 AM , Anonymous poh said...

there are some articles commented the command will make the query to execute up to 5 times.
http://www.ssistalk.com/2007/10/10/ssis-stored-procedures-and-the-ole-db-source/

 
At December 19, 2008 at 9:59 AM , Anonymous poh said...

I'm sorry, i make a mistake, those are refering to "set fmtonly on"

 
At May 15, 2009 at 10:07 PM , Anonymous Anonymous said...

I was getting exactly this problem and the SET NOCOUNT ON sorted it. Thanks

 
At July 30, 2009 at 6:33 AM , Anonymous Anonymous said...

Thanks. I had this same issue has been resolved now.

 
At November 7, 2009 at 12:46 AM , Anonymous Anonymous said...

viagra and cialis guaranteed cheapest viagra viagra overdose viagra liver damage does viagra really work alternative to viagra uk alternative viagra viagra 6 free samples viagra herb alternative buy viagra in canada viagra cheap buy online does viagra work viagra overnight viagra free pills

 
At February 16, 2010 at 7:57 AM , Anonymous Craigology said...

Thanks, big help!

 
At June 28, 2011 at 11:39 PM , Anonymous Anonymous said...

Mehroz bhai bohaut bohaut shukriya aap ka! mai kab se phasa wa tha is problem per.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home