3 min læsning

Using table variables in a SSIS data source

Featured Image

Every now and then standard queries just aren’t sufficient as data sources and therefore it might be necessary 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 looks 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 does return the expected values when executed in Management Studio and even when Previewing it from the source component:

image 

But when the packages are 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 these Messages, that tell us how many rows that are 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 shows you how to use table variables. In some cases, it might make more sense to use temporary tables. In my next blog post, I will show you how that is done, as this, unfortunately, isn’t done in the same way.

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.