November 22, 2011 at 2:07 pm
Hello -
I'm working on a SSIS Package I can Parse Query with no issue but as soon as I select OK I get the following message that is attached to this post.
I can take this same script and run it from SSMS with no issues and return data.
Thought or other ideas why I keep getting this?
Regards,
David
November 22, 2011 at 2:13 pm
Could it be a problem with spaces in the column names?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 22, 2011 at 2:19 pm
I wish it was that. I tried to remove the spaces with no such luck.
This is an image from the table that the view reads from (attached)
November 22, 2011 at 2:23 pm
david.ostrander (11/22/2011)
I wish it was that. I tried to remove the spaces with no such luck.This is an image from the table that the view reads from (attached)
That's not a table definition - is it a view definition? Its columns have spaces in their names...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 22, 2011 at 2:26 pm
If your asking am I querying a view for the SSIS package then yes I am.
November 22, 2011 at 2:39 pm
Confirm the metadata on the view is refreshed since the last changes. sp_refreshview
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2011 at 2:49 pm
Thank you I tried the sp_refreshview hgvTimeDataFetchWithRates_Rosetta it stated that it completed but I'm still getting the same errors.
November 22, 2011 at 2:59 pm
OK - but I asked about the column names and I'm not sure that you've tried what I suggested.
In your SQL for the extraction from the view, try aliasing the columns with spaces so that they have no spaces as far as SSIS is concerned:
[Cost Rate] CostRate,
[Line Item Status] LineItemStatus,
etc etc
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 1, 2011 at 7:36 am
Thanks to everyone for the replies. We found out that the table name was wrong. Once it was changed to the right database it worked. But everyone gave me other things to try if we run into this again.
Thanks,
David
December 7, 2012 at 2:36 pm
Just in case anyone else has gotten here for the same reason I did which was you're trying to build an SSIS package based on a stored procedure that is selecting the data from a temporary table (Select column1, column2, column3 from #tablename).
If you can, it worked better if we used a table variable instead:
DECLARE @tablename
(Column1 varchar(10),
column2 varchar(10),
column3 varchar(10)
)
Of course, the problem with that is that the data is put all in memory instead of TempDB, but it did resolve our issue.
December 8, 2012 at 5:37 am
T Childers (12/7/2012)
Just in case anyone else has gotten here for the same reason I did which was you're trying to build an SSIS package based on a stored procedure that is selecting the data from a temporary table (Select column1, column2, column3 from #tablename).If you can, it worked better if we used a table variable instead:
DECLARE @tablename
(Column1 varchar(10),
column2 varchar(10),
column3 varchar(10)
)
Of course, the problem with that is that the data is put all in memory instead of TempDB, but it did resolve our issue.
Unless your table variable is likely to contain fewer than about 100 rows, I would urge you to reconsider.
Your temp table problem will go away if you publish the meta data at the start of your stored proc (a different technique is needed in 2012 - see later).
Here's how to publish your meta data
-- Publish metadata for SSIS
if 1 = 0
begin
select
cast(null as bit) [Col1],
cast(null as int) [Col2],
etc etc
end
where the column names and data types line up with what your stored proc will return. Job done.
As I mentioned, in SSIS 2012 this technique no longer works. In fact, things have got tidier, because now the meta data is defined in the EXEC statement:
exec usp_proc1 with result sets
(
([Col1] bit,
[Col2] int
);
Now on to my final point. It's a common misconception that table variables are held in memory and do not hit tempdb. You might like to do some further reading on this, as others have done the investigative work far better than I. Try this[/url] for starters.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 19, 2013 at 10:59 am
Thanks for the additional information and will probably help someone in the future who's googling this error.
In my case, the table was not wide and in general would have less than 50 records and never more than 1500, so although I should have said the data can start out in memory, I would imagine for me it would have stayed in memory and not spilled over to the TempDB.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply