Saturday, June 16, 2012

Table Variable in Execute SQL Task SSIS

Yesterday one of my colleague ask me to resolve an issue with table variable in SSIS.

What was that issue, she was trying to use Table variable in "Execute SQL Task", everything was working but no data was loading in table.

After reading an article what we found that by just putting "SET NOCOUNT ON" statement on the top of TSQL script will resolve that issue.

Means when we simply run any SELECT statement then SQL Server returns two sets as an output , one is result of Select statement and second is "no of rows affected by a Transact-SQL statement".

SSIS treat last result as an original output which is not in actual , so by putting  "SET NOCOUNT ON" statement we can stop the last output .


Thanks,
Randhir

2 comments:

W. Burt said...

We were having problems with Table Variables in SSIS packages and your fix worked, thanks!

maxbrezel said...

Thanks, I didn't know that!