SQL Timeouts and SSIS

Question

Hi All,

I've an SSIS package that runs a stored proc for exporting to an excel file. Everything worked like a champ until I needed to a do a bit of rewriting on the stored proc. The proc now takes about 1 minute to run and the exported columns are different, so my problems are the following;

1) SSIS complains when I hit the preview button "No column information returned by command"

2) It times out after about 30 seconds.

What I've done.

Tried to clean up/optimize the query. That helped a bit, but it still is doing some major calculations and it runs just fine in SSMS.

Changed the timeout values to 90 seconds. Didn't seem to help. Maybe someone here can?

Thanks,

Answer 1

A shot in the dark based on something obscure I hit years ago: When you modified the procedure, did you add a call to a second procedure? This might mess up SSIS's ability to determine the returned data set.

As for (2), does the procedure take 30+ or 90+ seconds to run in SSMS? If not, do you know that the query is actually getting into SQL from SSIS? Might be worth firing up SQL Profiler to see what's actually being sent to SQL Server. [Which was the way I found out my obscure factoid.]

Answer 2

Found this little tidbit which helped immensely. No Column Names

Basically all you need to do is add the following to your SQL query text in SSIS.

SET FMTONLY OFF

SET NOCOUNT ON

Only problem now is it runs slow as molasses :-(

EDIT: It's running just too damn slow.

Changed from using #tempTable to tempTable. Adding in appropriate drop statements. argh...

Answer 3

Although it appears you may have answered part of your own question, you are probably getting the "No column information returned by command" error because the table doesn't exist at the time it tries to validate the metadata. Creating the tables as non-temporary tables resolves this issue.

If you insist on using temporary tables, you can create the temporary tables in the step preceeding the data flow. You would need to create it as a ## table and turn off connection sharing for the connection for this to work, but it is an alternative to creating permanent tables.