<img src="https://track.adform.net/Serving/TrackPoint/?pm=268420" width="1" height="1" alt="">

Using table variables in a SSIS data source is not returning any records

5 min. læsetid
26. marts 2013 Skrevet af: Inspari - Microsoft Del med en ven     

Every now and then standard queries just aren’t sufficient as data sources and therefore it might be neccesary to add an intermediate step. For instance by using a table variable. But using a table variable will by default not return any records. So what do we do, then?

If you do like me, you have probably written your source query in Management Studio first and tested that everything look exactly the way you expect. Then it is simply just a matter of pasting the query into the source component. At least that’s what I would think.

Below is an example of a query using a table variable. (I know – It’s pretty simple – But play along).

image

The query do return the expected values when executed in Management Studio and even when Previewing it from the source component:

image 

But when the packages is executed there are no records in my data flow as you can see below.

image

So what happens? In order to visualize what goes on, we should go back to Management Studio. If we look under the Message tab we see that we are getting more information about the query than we normally get from a plain SELECT statement. It is theese Messages, that tells us how many rows that is affected by the sql statement, that is causing the issue. The way to get rid of them, is to set NOCOUNT to ON. You can see the difference below.

clip_image002clip_image004

With that knowledge, I update my source component with ‘SET NOCOUNT ON’ and execute the package once more.

image

clip_image006

And finally I have the expected result.

This little example show you how to use table variables. In some case it might make more sense to use temporary tables. In my next blot post I will show you how that is done, as this unfortunately isn’t done in the same way.