CODEWITHSUNDEEP
sqlsql-servert-sqlssissql-server-2016
Jonathan Porter
Jonathan Porter

Reputation: 1556

SSIS Error: Invalid object name - but object exists and query runs in SSMS

I'm currently updating all of our ETLs using Visual Studio 2015 (made in BIDS 2008) and redeploying them to a new reporting server running on SQL Server 2016 (originally 2008R2).

While updating one of the ETLs I got this error:

Exception from HRESULT: 0xC0202009 Error at Load Staging Table [OLE DB Source [129]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid object name 'dbo.TimeSheets'.".

Here's what I've tried:

Upvotes: 7

Views: 13247

Answers (6)

Casey
Casey

Reputation: 81

Same issue.

I could see the corresponding tables in the drop-down in several of my OLE DB Destinations, but it would not let me see the mappings, giving the following error: "Exception from HRESULT: 0xC0202040... SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005."

What did not work:

  • Restarted Visual Studio
  • Several solution cleans/rebuilds
  • Restarted the computer

What did work:

  • Closed Visual Studio
  • Cleared files in %temp% folder

Details When clearing my %temp% files (those that would delete), one folder (VsHub) would not delete, but I was able to delete some of the files that it contained. When I started Visual Studio again and opened the sequence that contained those OLE DB Destinations, it was obvious that it was re-evaluating the XML code and the error went away.

Hope this helps someone.

Upvotes: 0

Stephan
Stephan

Reputation: 43

I just had the same issue.

Both SSIS and SSMS refused to find the table [Stage].[Customer], although it definetly existed (and had existed for quite some time).

Dropping and re-creating the table did no good. Also I noticed that SSMS's intellisense did not pick up that the table had been re-created (yes, after ctrl+shift+R)

What helped in the end was using

CREATE TABLE [Stage].[Customer] ([Id] INT)

in SSMS

This caused an error because the table already existed. Since then both SSMS and SSIS are working as expected. No idea what caused this error, but it is been difficult to track down.

Upvotes: 0

Jonno Lord
Jonno Lord

Reputation: 314

I tried several of the above suggestions but what worked for me in the end was the good old close and re-open of SSDT!

Upvotes: 0

Shalabh
Shalabh

Reputation: 1

For SSIS - Specially it is observed if any table name is coming as "Invalid object name" then Check as - First - Check table exist through SSMS and if not then SSMS> Edit > IntelliSense > Refresh Local Cache

Second - While making DB connection via "Connection manager" or already exist then - check table name comes in drop down of "Name of the table or View"

Upvotes: 0

aman
aman

Reputation: 61

I got a successful fix from the comments of the question "The other option would be to fully qualify the table name {database}.{schema}.{table} to ensure that, regardless of the default catalog, you query the correct database."

I was using some other database and it was caching and using that DB name rather than the new one I changed to.

Upvotes: 5

Sucharitha N
Sucharitha N

Reputation: 11

I encountered the same issue - my Database was somehow, not getting picked up. So I manually added the database and test the connection and now it works fine.

Upvotes: 1

Related Questions