I had faced an issue with SSIS in which i needed to use table
variables( as i dont have permission to create CTE). I wrote the query using
four table variables and i got the proper output in management studio. This
query was running for 54 seconds to retrieve the necessary data. But, when i
implement this in SSIS OLE DB source, the data from table variables is not
getting inserted in the destination table and i dint get any error as well when
ran the SSIS package as a whole. When executed the package, it executed
successfully. But in the data flow path it was not showing the number of rows
returned from source. When checked in the destination table , It was empty.
When i debugging i
found that this table variables were getting timeout (found when i used preview
option) and its not taking more than 10 seconds to throw the error. I was
wondering because, in management studio it took 54 secs and in SSIS it throws
error in 10 seconds. I tried setting up the timeout property and changed the
query to fix the issue and nothing worked.
Finally found one
article in MSDN in which its mentioned that table variables will return record count if the SET RECORDCOUNT option is set to off. So, i put that option is on of top of the page. Interestingly, still i was not able to ran package and getting timeout error. Then i put SET RecordCOUNT OFF just before all the table variables( i used four table variables). This solved my problem.
Shop Amazon - Save on Temptations and Dentastix Products
