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.

Advertisements

11 Responses to “SSIS: How to use a query containing table variables for an OLE DB Source”

  1. Jason Says:

    This was what hit me yesterday! thanks!

  2. Rob Says:

    This hint has been very useful- thanks for doing the hard yards and working it out!

  3. Guido Says:

    Used to work in SSIS 2005, but no longer in SSIS 2008, any other ideas?

  4. Craig Says:

    Fixed my problem in SSIS 2008, thanks!

  5. Guido Says:

    Didn’t work for me either!

  6. Nigel Booty Says:

    I was having exactly this problem and couldn’t figure it out in SSIS 2008 so many thanks for the answer. Worked for me!

  7. Yogesh Says:

    Its not working for me and I am stuck with this problem now.

  8. Satya Kota Says:

    Nice Hint.. very use ful

  9. Randhir Singh Says:

    OMG…. Thanks man …

  10. Jose Rodgers Says:

    i wanna kiss your mother!!!! ThanKSSSSSSSSSSSSSSSSS

  11. Andrzej Says:

    Thumb up!!!! Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: