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

▲
5
▼

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:

  • Checked my connection strings to make sure they were correct.
  • Checked the schema to make sure it existed and was correct.
  • Ran query from SSMS and it worked.
  • Ctrl + Shift + R to refresh intellisense.
  • Checked to see if another table exists with the same name.
  • Restarted Visual Studio and SSMS.
sql
sql-server
tsql
ssis
sql-server-2016
asked on Stack Overflow Dec 29, 2016 by Jonathan Porter • edited Dec 29, 2016 by Jonathan Porter

4 Answers

▲
3
▼

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.

answered on Stack Overflow Aug 7, 2018 by aman • edited Aug 7, 2018 by mrk
▲
1
▼

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.

answered on Stack Overflow Sep 5, 2017 by Sucharitha N
▲
0
▼

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"

answered on Stack Overflow Oct 24, 2018 by Shalabh
▲
0
▼

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!

answered on Stack Overflow Mar 16, 2021 by Jonno Lord

User contributions licensed under CC BY-SA 3.0